Delphi programmers often store TDateTime (be it date, time or date+time values) as floating points in the database. They take the easy way out basically because TDateTime is nothing but a typed alias for the Delphi Double type (which is equivalent to the IEEE 754 double-precision binary floating-point format: binary64 type) .
Actually, TDateTime is equivalent to the OLE Automation date (which is described in the remarks of DateTime.ToOADate Method (System)) which has an epoch of midnight at 1899-12-30 (to be compatible with Lotus-123 including its leap-year-bug, background on that is at Why are the Microsoft Office file formats so complicated? (And some workarounds) – Joel on Software).
Anyway: the definition is this:
An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.
The base OLE Automation Date is midnight, 30 December 1899. The minimum OLE Automation date is midnight, 1 January 0100. The maximum OLE Automation Date is the same as DateTime.MaxValue, the last moment of 31 December 9999.
The sample values are remarkably the same as the ones for TDateTime:
| Value |
Description |
| 0 |
December 30, 1899; 12:00 A.M. |
| 2.75 |
January 1, 1900; 6:00 P.M. |
| -1.25 |
December 29, 1899; 6:00 A.M. |
| 35065 |
January 1, 1996; 12:00 A.M. |
The below SQL example uses the Firebird date/time/timestamp casting shortcuts:
select date '1899-12-30' + 42452.670590278 as Date20160323,
timestamp '1899-12-30 00:00:00.000' + 42452.670590278 as DateTime20160323160539,
time '00:00:00' + 60*60*24 * 0.5 as Noon,
timestamp '1899-12-30 00:00:00.000' + 0 as DateTime18991230Midnight,
timestamp '1899-12-30 00:00:00.000' + 2.75 as DateTime190001011800,
timestamp '1899-12-30 00:00:00.000' + -1.25 as DateTime189912290600,
timestamp '1899-12-30 00:00:00.000' + 35065 as DateTime19960101Midnight
from rdb$database
Which – using DMY date format and 24 hour clock format settings – results in:
DATE20160323 DATETIME20160323160539 NOON DATETIME18991230MIDNIGHT DATETIME190001011800 DATETIME189912290600 DATETIME19960101MIDNIGHT CONSTANT
---------------------------------------------------------------------------------------------------------------------------------------------------
24-3-2016 23-3-2016 16:05:39 12:00:00 30-12-1899 1-1-1900 18:00:00 28-12-1899 18:00:00 1-1-1996
Which basically taught me a new thing about firebird: Times are calculated in seconds, so date fractions need to be multiplied by 60 * 60 * 24.
You can see this in the following query and results:
select (cast('Now' as time) - cast('00:00:00.000' as time)) / (24 * 60 * 60) as DelphiTime,
cast('Now' as time) - cast('00:00:00.000' as time) as SecondsSinceMidnight,
cast('Now' as date) - cast('1899-12-30' as date) as DelphiDate,
cast('Now' as timestamp) - cast('1899-12-30 00:00:00.000' as timestamp) as DelphiDateTime,
24 * 60 * 60 as SecondsPerDay,
cast('Now' as date) as "Date",
cast('Now' as time) as "Time",
cast('Now' as timestamp) as "TimeStamp"
from rdb$database
DELPHITIME SECONDSSINCEMIDNIGHT DELPHIDATE DELPHIDATETIME SECONDSPERDAY Date Time TimeStamp
------------------------------------------------------------------------------------------------------------------------------------------------
0,4366 37722,284 42835 42835,436600509 86400 10-4-2017 10:28:42 10-4-2017 10:28:42
Note this post is complementary to Date format converter from Text or Unix/Mac/Filetime/Microsoft to virtually any readable form (which mentions the wrong Microsoft epoch as it should be 1899-12-30 midnight): the above shows how to do the conversion to readable dates in Firebird (might work in InterBase as well, but I’ve not used that for a long time).
–jeroen
PS: for Microsoft SQL Server: passing dates to the sql server: pass it as float – 2. -2 is the difference between delphi tdatetime start date and mssql start date.
The Wiert Corner - irregular stream of stuff 