SQLite: All dates being retrieved as 0

[djr]

This is a sort of embarrassing question to ask because I'm sure it must be something very simple I'm missing here.  I set up a simple DBGrid following the instructions in the SQLDB tutorial and I am simply displaying a "SELECT * FROM

query.  All columns display correctly except my date column in which all values appear as 30-12-99.  All of the dates appear correct in DB Browser for SQLite, SqlQuery1.Fields[2].AsString gets me the same '0' value, as does .AsDateTime.  When the DB was created a string formatted "MM/DD/YYYY" was inserted in to a DATE column, and I know SQLite doesn't have a true Date type, but I just don't understand why I am seeing all values as 0?

[LacaK]

When the DB was created a string formatted "MM/DD/YYYY" was inserted in to a DATE column, and I know SQLite doesn't have a true Date type,

MM/DD/YYYY is not supported format of date values in SQLite (it must be YYYY-MM-DD)
See https://www.sqlite.org/datatype3.html#section_2_2

[djr]

MM/DD/YYYY is not supported format of date values in SQLite (it must be YYYY-MM-DD)
See https://www.sqlite.org/datatype3.html#section_2_2

Thanks for your help, I read those sentences a thousand times and never made the connection in my mind that the pattern is the ONLY one I can use.  I ended up just dropping all the fancy DB components and using a wrapper for the C bindings since all I'm doing is reading the database in to my own date structure.  sqlite3_column_text() returns the MM/DD/YYYY string that I originally inserted.  I'm guessing that's dynamic typing at work there and my DATE column might as well just be TEXT, which is fine since all the processing is done in my app so I don't need any of SQLite's date functions. 

I am thinking TSQLQuery was designed with the static typing of the majority of RDBMSs so running .asString on my date column is returning zero because it's not a valid date in that column.  So I guess my next question is: Is it possible to do what I'm doing here with TSQLQuery?  That is, pull data of type TEXT from a column typed DATE?

[Thaddy]

Try just something like:

Code: Pascal  [Select][+][-]

1. DateString:=Query.FieldByName('DATE').AsString;

But as Lacak pointed out, your DATE isn't a properly typed DATE at all inSqlite ...It is a string type.
Get it out of your head  it is typed DATE. It isn't. It's a string.
And your format is also incompatible with the Sqlite date conversion functions.
If you use a properly typed DATE, be it TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"), REAL or INTEGER then you have a properly typed DATE which is easy to convert to a representional format through FormatDateTime combined with Field.AsDateTime.