Home > DeveloperSection > Forums > Same queries displaying displaying different date in results?
Linda Perla
Linda Perla

Total Post:13

Points:93
Posted on    September-30-2015 12:16 AM

 SQLite Database  Sqlite  Sqlite3 
Ratings:


 1 Reply(s)
 381  View(s)
Rate this:

I have a problem with different results from two tables, both tables have three rows but different date formats here is the code:

sqlite> create table table1 (date text);
sqlite> create table table2 (date text);

Three rows with dates but the different formats:

sqlite> select * from table1;
28.09.2015
28.08.2015
29.08.2015
sqlite> select * from table2;
2015-09-28
2015-08-28
2015-08-29

My current date is:

sqlite> select date('now');
2015-09-29

Why do I have a different results for the next similar queries?

sqlite> select * from table1 where date < strftime('%d.%m.%Y', 'now',

'-1 day');
28.08.2015

sqlite> select * from table2 where date < strftime('%Y-%m-%d', 'now',

'-1 day');
2015-08-28
2015-08-29

Why first query don't returns '29.08.2015' too?



Tarun Kumar

Total Post:210

Points:1614
Posted on    September-30-2015 11:03 PM

SQLite has no date type. When you do date < strftime(...) you're doing a string comparison. The ISO 8601 date format like 2015-08-28 will compare as dates when compared as strings. 28.08.2015 will not, it will weigh the day first, then the month, then the year.

strftime only understands a limited set of formats (see "Time Strings") and 28.09.2015 isn't one of them. Either store all dates in ISO 8601 format, or follow the answers to this question cover how to compare dates in SQLite.

Don't want to miss updates? Please click the below button!

Follow MindStick