* * *

Author Topic: SQLite nd DateTime weirdness  (Read 420 times)

KarenT

  • Jr. Member
  • **
  • Posts: 52
SQLite nd DateTime weirdness
« on: August 03, 2017, 03:41:20 pm »
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

  • New member
  • *
  • Posts: 15
Re: SQLite nd DateTime weirdness
« Reply #1 on: August 09, 2017, 06:01:11 pm »
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.  

Debian Squeeze; Windows Vista/XP
Lazarus 1.0; FPC 2.6.0

LacaK

  • Hero Member
  • *****
  • Posts: 543
Re: SQLite nd DateTime weirdness
« Reply #2 on: August 16, 2017, 09:49:21 am »
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 ...

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus