CODEWITHSUNDEEP
datedatetimesqlite
Nicolas Desbaillets
Nicolas Desbaillets

Reputation: 3

sqlite3 select condition based on date time('now') bug

I'm new to sqlite. I have a table called "matchs" containing the upcoming games in a club championship. I would like to retrieve the first next upcoming event.

The first column is date_session formatted as "YYYY-MM-DD HH:MM:SS"

So I made query like this:

SELECT * FROM matchs WHERE date_session > datetime('now');
    2017_01_09 18:30:00
    2017_01_09 19:30:00
    2017_01_10 18:20:00
    2017_01_10 20:40:00
    2017_01_12 18:20:00
    2017_01_12 20:40:00
    2017_01_16 18:30:00

I don't understand why the rows from January 9th are included in the results. To check that date time('now') is working I just outputted it:

SELECT datetime('now');
    2017-01-10 09:30:55

If I do the query on a condition date set by hand, it works fine:

SELECT * FROM matchs WHERE date_session > '2017_01_10 09:30';
    2017_01_10 18:20:00
    2017_01_10 20:40:00
    2017_01_12 18:20:00 
    2017_01_12 20:40:00
    2017_01_16 18:30:00

Of course, as I wish to always have the next upcoming game returned, I can't afford setting this date condition manually. Can anyone help? I'm using SQLite version 3.15. date_session is stored as TEXT.

Upvotes: 0

Views: 51

Answers (1)

CL.
CL.

Reputation: 180030

- and _ are different characters. In any string comparison, _ > -, so you end up comparing only the year.

Upvotes: 1

Related Questions