SQLite datetime comparisons

Showing 1-4 of 4 messages
SQLite datetime comparisons neilrhansen 1/2/08 12:02 PM
I just found an issue with my code with the new year.
I am using the SQL db for android to store some data.  Of the columns
in my data base is a datetime.

I store the datetime in the format "11/23/2007 5:30 PM".

I always queried using:

SELECT DateCol, DataCol
FROM MyDatabase
WHERE DateCol <= '12/30/2007 12:00 AM'

And I recieved my data exactly how I expect it to.
But, if I replace my query with '01/01/2008 12:00 AM', the query
yields no data.

Am I using my WHERE clause appropriately?
Re: [android-developers] SQLite datetime comparisons Rick Genter 1/2/08 12:23 PM


Your expectations are wrong.

SQLite treats dates as strings. If you don't store your date/times  
using ISO-8901 format (YYYY-MM-DD HH:MM:SS), you can't compare them,  
since only in ISO-8901 format do lexical comparisons work as expected.
--
Rick Genter
rick_...@comcast.net

Re: SQLite datetime comparisons neilrhansen 1/2/08 12:33 PM
Very good.  I'll give it a try.  Thanks a bunch!

Neil
Re: [android-developers] Re: SQLite datetime comparisons Rick Genter 1/2/08 1:29 PM