I'm writing a Delphi program that needs to interact with data stored in a SQLite database that belongs to another program. Everything ok so far, except that I'm unable to get the date/time value from the data store in a column in the SQLite database, with the data type 'datetime'.
I do know that the data type of fields is not relevant in SQLite, and that everything is stored as strings, and perhaps that is possibly the reason why I find myself in this predicament.
Below is a sample of a few rows of the data stored in the SQLite database (column 3) vs. the corresponding date value displayed in the program (column 2) that reads and writes to this SQLite database:
1 11/7/1971 621939168000000000
2 3/17/1976 623314656000000000
3 5/4/1996 629667648000000000
4 9/21/2007 633259296000000000
5 11/17/1972 622264032000000000
6 2/7/1996 629592480000000000
7 6/13/2000 630964512000000000
My requirement: Once I read the value in column 3 from my Delphi program, how do I translate it to the same date/time value as displayed in column 2? I have tried the UnixToDateTime function, but it does not result in the same value (as column 2). Can anyone help?
Thanks in advance!
CodePudding user response:
The value in column 3 is the number of 100 nanoseconds since 1/1/0001. You can convert it into a Delphi TDateTime
like this:
theDate := (Value/864000000000) - 693593;