thanks Jean-Christophe Deschamps
i have changed my DT form to YYYY/MM/DD hh:mm:ss
and all query are working.
Jean-Christophe Deschamps wrote:
>
>
>> 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
>
>
--
View this message in context:
http://old.nabble.com/Between-Query-failed-for-DATETIME-in-sqlite-tp26897635p26902880.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
