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 :-)