SQLite nd DateTime weirdness
lazarus / 16-Databases

----------------------------------------------------------------------
KarenT · 2017-08-03 13:41

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   // For
example, DBBrowser For SQlite shows a Date as 9/23/2018  // Lazarus
shows  DateTimePicker1.Date:=sqlApps.FieldByName(fldDate).AsDateTime;
 // 8/3/2017  Label1.Caption:=DateTimeToStr(sqlApps.FieldByName(fldDat
e).AsDateTime);  // 12/30/1899 
Label2.Caption:=FloatToStr(sqlApps.FieldByName(fldDate).AsFloat);  //
0  Label3.Caption:=sqlApps.FieldByName(fldDate).AsString;  //
12/30/1899

----------------------------------------------------------------------
bracara · 2017-08-09 16:01

Sqlite and Object Pascal use different ways of storing time. You need
to convert between them. Try these functions:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
JulianDateTimeToDateTime ( juliandatetime: Double ) : TDateTime;begin 
Result := juliandatetime-2415018.5;end; 
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
DateTimeToJulianDateTime ( datetime: TDateTime ) : Double;begin 
Result := datetime + 2415018.5;end;

----------------------------------------------------------------------
LacaK · 2017-08-16 07:49

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 ...