CODEWITHSUNDEEP
sqlitedatetimedatecompare
qkx
qkx

Reputation: 2563

SQLite date compare very strange

I store dates as String in my database, in this format:

YYYY-MM-DD HH:MM

and in my db I have rows (all columns are strings):

       COL1        |     COL2
----------------------------------
'2012-06-21 18:53' |   'item1'
'2012-06-21 18:54' |   'item2'
'2012-06-21 18:55' |   'item3'

Now I want to compare these stored dates (well, strings), and this is very very strange:

this query

select * 
  from MyTable 
  where col1 > Datetime('2012-06-21 18:53')

returns 2 rows (all except first) - this is correct.

but this query 

select * 
      from MyTable 
      where col1 >= Datetime('2012-06-21 18:53')

return also only 2 rows, but it should return all 3 rows, as I used >= instead of >.

What did I wrong?

Upvotes: 1

Views: 837

Answers (1)

MaxSem
MaxSem

Reputation: 3547

sqlite> SELECT datetime('2012-06-21 18:53');
2012-06-21 18:53:00

datetime() returns a string in a different format than the fields of your database. You can use just the string for WHERE, e.g. 

select * 
      from MyTable 
      where col1 >= '2012-06-21 18:53'

Upvotes: 1

Related Questions