Date's represented as text (and the textual representation of a date) in SQLite
are big endian (ISO8601). THis means that the biggest part goes on the left,
the next biggest to the right of that, and so on. Time is in the 24 hour clock
and AM/PM are not used. THis results in sortable strings as long as the GMT
offset is always the same (for a localized datetime) or always GMT if no offset
is specified (ie, the timestring is naive). You could try using strptime (from
the standard C library) to parse the date into a normal format, or ask for the
datetime as a unix epoch time or a JD/MJD/RD etc datetime number. See
https://www.sqlite.org/lang_datefunc.html for how SQLite handles datetime data.
Microsoft, being a weeny company that only exists in a single timezone is
notoriously bad at handling date/time data (they have never produced or
released a product that can handle date/times properly, and the few they have
bought they have immediately made "Microsoft Compatible" and rendered
completely useless at handling date/time information), however I cannot believe
that you cannot set the format in which you want the data returned to you. You
want either ISO8601 or JIS. JIS (Japanese Industrial Format) being the closest
thing that Microsoft comprehends to a rational datestring format.
You can also request the datetime in huns (hundredths of a second from
1980-01-01 00:00:00 GMT) and convert that into either a unix epoch or JD
variant as numeric data is intrinsically sortable.
You can also, rather than using MS program derived data to get "now" to store
in your create date field, source it from the sqlite function datetime('now')
which will return the correct ISO8601 GMT timestring.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of DSI
>Sent: Wednesday, 25 February, 2015 10:14
>To: sqlite-users at sqlite.org
>Subject: [sqlite] Date - invlaid datetime
>
>Good Morning,
>
> I am building a Windows Universal App using Azure Mobile
>Services and implementing offline data using sqlite as a local database.
>Everything is working great. Thank you for making this available. When
>I
>Sync the data everything works well except for the created date column
>which
>has a format of "2/20/2015 2:13:26 AM +00:00". When I attempt to view
>the
>data in the sqlite table I receive an error stating that
>"System.FormatException: String was not recognized as a valid DateTime".
>We
>have no control over how azure stores the date. As a work around I have
>also been storing the date as a string in a text field but this makes
>trying
>to sort by date impossible. I would like to sort by the created date but
>cannot if sqlite sees it as an invalid date.
>
>
>
>Any help you can offer would be greatly appreciated.
>
>
>
>Thank you
>
>David
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
