SQLite nd DateTime weirdness

[KarenT]

Ubuntu16.04, Laz 1.8.
I just imported some CSV data with a Date field format of  "mm/dd/yyyy"

When I look at the Data in "DBBrowser For SQlite" it has converted the string data from the .CSV into the correct Dates for each Row as a "DateTime" as the column is defined.

When I look at it in Lazarus I get "12/30/1899" for all of the dates.

What do I need to do to get Lazarus to show the correct dates as "DBBrowser For SQlite" shows it?

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

1.   // For example, DBBrowser For SQlite shows a Date as 9/23/2018
2.   // Lazarus shows
3.   DateTimePicker1.Date:=sqlApps.FieldByName(fldDate).AsDateTime;  // 8/3/2017
4.   Label1.Caption:=DateTimeToStr(sqlApps.FieldByName(fldDate).AsDateTime);  // 12/30/1899
5.   Label2.Caption:=FloatToStr(sqlApps.FieldByName(fldDate).AsFloat);  // 0
6.   Label3.Caption:=sqlApps.FieldByName(fldDate).AsString;  // 12/30/1899
7.  

[bracara]

Sqlite and Object Pascal use different ways of storing time. You need to convert between them. Try these functions:

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

1. function JulianDateTimeToDateTime ( juliandatetime: Double ) : TDateTime;
2. begin
3.   Result := juliandatetime-2415018.5;
4. end;
5.  

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

1. function DateTimeToJulianDateTime ( datetime: TDateTime ) : Double;
2. begin
3.   Result := datetime + 2415018.5;
4. end;
5.  

[LacaK]

If you use sqlDB with SQLite3Connection component then read also:
 http://wiki.freepascal.org/SQLite#SQLite3_and_Dates
sqlDB follows SQLite3 specification for storing date/time values:
 http://www.sqlite.org/datatype3.html#datetime

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

So if "DBBrowser For SQlite" stores values in other format you will have troubles ...