Question / Problem
I would like to read the SpaceObserver tables in SQL server to show the SpaceObserver data with data from an other database.The problem is that when I try get the datetime from the fields in the database by casting them to datetime the result is incorrect.What is the function to translate the floats to the correct datetime?
Answer / Solution
Date time values:
The representation of dates in the SpaceObServer database is equal to the Delphi TDateTime specification. One reason for this is that it is much easier to support different SQL databases.
In Delphi, TDateTime is a type that maps to a Double. In C++, the TDateTime class corresponds to the Delphi TDateTime type. The integral part of a Delphi TDateTime value is the number of days that have passed since 12/30/1899. The fractional part of the TDateTime value is fraction of a 24 hour day that has elapsed. Following are some examples of TDateTime values and their corresponding dates and times:
0 12/30/1899 12:00 am
2.75 1/1/1900 6:00 pm
-1.25 12/29/1899 6:00 am
35065 1/1/1996 12:00 am
To find the fractional number of days between two dates, simply subtract the two values, unless one of the TDateTime values is negative. Similarly, to increment a date and time value by a certain fractional number of days, add the fractional number to the date and time value if the TDateTime value is positive. When working with negative TDateTime values, computations must handle time portion separately. The fractional part reflects the fraction of a 24-hour day without regard to the sign of the TDateTime value. For example, 6:00 am on 12/29/1899 is –1.25, not –1 + 0.25, which would be –0.75. There are no TDateTime values between –1 and 0.
To convert this value to the MS SQL Server data type datetime us the DATEADD() function:
SELECT DATEADD(dd , LASTSCAN_TIME, '18991230 ') FROM ROOTS
SELECT DATEADD(hh , LASTSCAN_TIME*24, '18991230 ') FROM ROOTS
SELECT DATEADD(mi , LASTSCAN_TIME*24*60, '18991230 ') FROM ROOTS