SQlite Query select string date today or less than

Question

Im having problem in query i cannot get the proper result dates i want I need to get all the date from today or less than date today

can you help me or give me right query to use

for example this is mytable

id(number) datestring(string)
1          02/06/2019
2          02/06/2019 
3          02/14/2019
4          02/04/2019
5          03/17/2019

query i use: SELECT * FROM mytable WHERE datestring <= date('now')

expected result is id(number) datestring(string) 1 02/06/2019 2 02/06/2019 4 02/04/2019

Thank you for helping

You should store your date values in a format understood by sqlite: sqlite.org/lang_datefunc.html Of course comparing a mm/dd/yyyy format string to a yyyy-mm-dd one isn't going to work right. — Shawn
– Shawn, Commented Feb 6, 2019 at 2:57
ah hmmm okay let me try it . am also confuse on my format and datetype . :) be rigthback — Jakegarbo
– Jakegarbo, Commented Feb 6, 2019 at 2:58
ok decide to adjust my code to proper format. :) thanks @Shawn — Jakegarbo
– Jakegarbo, Commented Feb 6, 2019 at 3:22

MikeT · Accepted Answer · 2019-02-06 05:59:59Z

The issue you are encountering is that date('now') will return 2019-02-06 , as such as all rows in the table start with 0 and that this is less than 2.

You either need to convert on of the dates to be in the same format as the other or use the same format when storing the data.

SQLite itself has various date format that can be recognised and used and it is advisable to utilise one of these as reduces necessary complexity.

Time Strings A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SQL As Understood By SQLite - Date And Time Functions

The following is a solution that could be used to convert the datestring column within the query :-

SELECT * FROM mytable WHERE substr(datestring,7,4)||'-'||substr(datestring,1,2)||'-'||substr(datestring,4,2) <= date('now');

The following could be used to convert the existing data to a recognised format :-

UPDATE mytable SET datestring = substr(datestring,7,4)||'-'||substr(datestring,1,2)||'-'||substr(datestring,4,2);

This could be followed by your original query

SELECT * FROM mytable WHERE datestring <= date('now');

Collectives™ on Stack Overflow

SQlite Query select string date today or less than

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related