> I am inserting data into asset table as m/d/yyyy HH:MM:SS t format
>Ex: INSERT INTO ASSET (WARRANTSTDATE) VALUES ('12/22/2009 12:01:00 AM')
>Ex: INSERT INTO ASSET (WARRANTSTDATE) VALUES ('9/22/2009 12:01:00 AM')
>but my select query failed : No record found
Not surprising: you made it very hard!
>SELECT * FROM asset where WARRANTSTDATE BETWEEN '8/02/2009 12:01:00 AM'
>AND '10/01/2009 12:01:00 AM'
You're comparing _strings_ which don't have a suitable format.
Your datetime "format" (is that one?) can't be ordered by the default
(binary) sort order.
Either store your dates in naturally sortable format YYYY/MM/DD
hh:mm:ss _and_ padd fields with a leading zero when needed _and_ use a
24h format
or
dissect your strings to get Y, M, D, h, m, s parts in this order (will
be much slower)
or
use a numeric format like epoch.
Bottom line: SQLite doesn't embark a brain able to decode dates/times
stored as strings the way you or me would understand them. Your format
is just a string for SQLite. So if you need to order them, you must
make it easy for SQLite to compare.
Read the "Date & Time functions" page in SQLite help.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
