An application I support uses a BIGINT to store date and
time data. Our installation is SQL
Server 2005, so the lack of accuracy in the datetime data type is probably a
driver behind this.
A small challenge when working with the application’s
database is working out the date and time against each record. This requires a conversion from the BIGINT field
to the datetime data type. The maths
involved is not too complex, but the trick is knowing the accuracy of the data
along with the starting point for a zero value.
In my case the values were accurate to the nearest 100-nanosecond
interval, from 1^{st} January 1601 (UTC). This corresponds to the FILETIME windows
structure, and as such is easily converted to a readable system date and time
within .NET using an API function. However,
in TSQL it’s a little more tricky…
We firstly need to adjust the BIGINT value to correspond to
the SQL Server datetime semantics, in the sense that the zero value in the
datetime data type represents 1^{st} January 1900. We must remove this offset before we can
break down the number into its constituent parts. To do this, we must subtract 94,354,848,000,000,000 from the BIGINT
value. You can work this out yourself
quite easily as it’s the number of 100-nanosecond intervals between 1601 and
1900 (299 years). Don’t forget to add in
the extra days for the leap years though! (72 by my count)
For datetime accuracy, we can now calculate the number of
milliseconds by dividing by 10,000. For
seconds divide the result by 1,000, minutes by a further 60, and so on.
A small error check to put in place is to ensure the
datetime does not fall prior to our minimum datetime value of 1^{st}
January 1753. Therefore as long as your
initial result does not fall below -53,690
days then we can continue
processing, else we need to either throw an error or default the output value
in some way. I personally prefer the
simpler approach of ensuring the initial result is positive else default to
zero and continue. The function below
does exactly that:
CREATE FUNCTION [dbo].[BigIntTimeToDateTime] (
@biFileTime bigint
)
RETURNS datetime AS
BEGIN
DECLARE @output AS datetime
DECLARE @ms AS bigint
DECLARE @min AS int
DECLARE @day AS int
IF @biFileTime IS NULL
BEGIN
SET @output = NULL
END
ELSE
BEGIN
-- remove difference between 1900 and 1601, then divide by 10,000 to get milliseconds
SET @ms = (@biFileTime - 94354848000000000)/10000
-- check that result is positive else return 1st Jan 1900 as the datetime
IF @ms < 0
BEGIN
SET @output = CAST(0 AS datetime)
END
ELSE
BEGIN
-- calculate the minutes
SET @min = CAST(@ms/60000 AS int)
-- calculate the days
SET @day = CAST(@min/1440 AS int)
-- starting from 0 (zero), add our days, minutes and milliseconds to get the datetime value.
-- NOTE - we use the mod operator to calculate the offsets of each time portion for the day.
SET @output = DATEADD(ms, CAST(@ms%60000 AS int), DATEADD(mi, CAST(@min%1440 AS int), DATEADD(dd, @day, CAST(0 AS datetime))))
END
END
RETURN @output
END
Should you ever need a function like this, then the
calculations are easily modified to suit your requirements. I’ve considered writing a generic function to
return a datetime based on input parameters to identify the accuracy of the
number and the date represented by zero.
One day I might get around to it, so I’ll save that for a later post :-)