Delphi's TDateTime in a datetime column: how to manipulate from SQL?

2013-03-19 22:25  i3065     [permalink]

Thanks to sqlite's manifest typing, it's possible to insert Delphi's TDateTime values in a field defined by SQL as datetime. I was using this for a while without any problem before I found out sqlite was actually storing a floating point value. There may be a problem though when you try to manipulate these dates with SQL. I searched around for a suitable conversion, and using default sqlite features, I came up with this code:

datetime('1900-01-01','+'||(myDateField-2)||' day')

where myDateField is a column or value of type datetime.

(At least, I have this with my own SQLite connector, if you use something else, your milage may vary.)

→