Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
#SQL Server 4
tsql 4
SQLServer 3
SQL Server 2
dba 2
backup 1
permissions 1
security 1

Archive · View All
January 2011 3
November 2012 1
June 2012 1
July 2011 1
February 2011 1

Converting a BIGINT datetime

Jan 11 2011 12:52PM by Mike Lewis (@SQLTuna)   

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 1st 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 1st 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 1st 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 
	DECLARE @output AS datetime
	DECLARE @ms AS bigint
	DECLARE @min AS int
	DECLARE @day AS int

	IF @biFileTime IS NULL
		SET @output = NULL
		-- 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
			SET @output = CAST(0 AS datetime)
			-- 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))))
	RETURN @output


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

Tags: tsql, #TSQL, SQLServer, #SQL Server, BRH,

Mike Lewis (@SQLTuna)
42 · 4% · 1336



  • This comment is waiting for moderation.

    commented on Nov 26 2014 5:28AM
    2827 · 0% · 4

Your Comment

Sign Up or Login to post a comment.

"Converting a BIGINT datetime" rated 5 out of 5 by 1 readers
Converting a BIGINT datetime , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]