CODEWITHSUNDEEP
sqlsqlitestrftime
dimadvk
dimadvk

Reputation: 415

Different results for a similar queries depends on format of date string in sqlite3

I have two tables in sqlite3:

sqlite> create table date_1 (date text);
sqlite> create table date_2 (date text);

Each table contains three rows with dates written in different formats:

sqlite> select * from date_1;
28.09.2015
28.08.2015
29.08.2015
sqlite> select * from date_2;
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 date_1 where date < strftime('%d.%m.%Y', 'now', '-1 day');
28.08.2015
sqlite> select * from date_2 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?

Upvotes: 1

Views: 74

Answers (1)

Schwern
Schwern

Reputation: 164729

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.

Upvotes: 1

Related Questions