Converting Active Directory Timestamps in Microsoft SQL Server

If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you!


INTRODUCTION

As AD admins or those having to deal with AD data, you probably have had to convert a timestamp or two like last logon to a logical date and time value versus some long integer value in the past.   I am sure some of you have found your ways using .NET CLR programming within SQL; however, this article aims to show you how to easily do so using T-SQL syntax.

As usual for those like me with a short attention span and since a beginner level of reading/using T-SQL code is a pre-requisite for reading this article, you can skip right to the handy user defined function to add to your toolkit by doing a find on "Appendix A: Get Datetime From AD Timestamp Function" or the "Quick In-line Solution" without the quotes.

For those wanting some background, we will begin now...


THE METHODOLOGY

This is just a simple mathematical problem, really, that in implementing in SQL server, we have to be cognoscente of the idiosyncrasies of its syntax like the integer division or date 0 difference of SQL Server to that of the ANSI standards.  

Important Facts:


Resulting Equation(s):

{# days since ANSI 0} = {AD timestamp} / {# 100ns/day}
                      {# days since SQL 0} = {# days since ANSI 0} - {# days from ANSI 0 to SQL 0}

Select all Open in new window

Quick In-line Solution:
Given, the last bullet above, the formula looks as follows in T-SQL:

DECLARE @timestamp BIGINT
                      SET @timestamp = 128881226539059241
                      ;
                      
                      SELECT CAST((@timestamp / 864000000000.0 - 109207) AS DATETIME)
                      ;

Select all Open in new window

Simple!

Notice the 864000000000.0 which represents the number of 100 nanoseconds per day has '.0' at the end to ensure we don't get integer division.  The 109207 is our calculated number of days between 1601-01-01 and 1900-01-01, including leap days.  For more details, please see code comments in the user defined function code of Appendix A.

The results (GMT):

2009-05-30 02:04:13.903

Select all Open in new window

MORE INFORMATION / VERIFICATION

If you were not already familiar with the mathematics or background on this subject, see the attached PDF of the Microsoft TechNet article on the subject since the link is no longer available online.
  lastLogonTimestamp.pdf

Aside from the background information in the linked article, you fill find you can use the script shown with extra "Wscript.Echo" I added to display the timestamp to verify that our new handy function is accurate.

Set objUser = _
                         GetObject("LDAP://cn=Ken Myer, ou=Finance, dc=fabrikam, dc=com")
                      Set objLastLogon = objUser.Get("lastLogonTimestamp")
                      
                      intLastLogonTime = objLastLogon.HighPart * (2^32) _
                         + objLastLogon.LowPart
                      
                      'Added this echo
                      Wscript.Echo "Timestamp: " & intLastLogonTime
                      
                      intLastLogonTime = intLastLogonTime / (60 * 10000000)
                      intLastLogonTime = intLastLogonTime / 1440
                      
                      Wscript.Echo "Last logon time: " & intLastLogonTime _
                         + #1/1/1601#

Select all Open in new window

*NOTE: change LDAP path to correct value for your environment.

Using this timestamp as the parameter to our function, we can validate that the SQL date generated is consistent with the date displayed by the script.

For those of you that read the TechNet article closely and are puzzled as to why the example shown doesn't work in our function (or at least appears not to work given the article says 2009-05-30 14:04:13.90 is the result), use the script above with an explicit intLastLogonTime of 127588712492538000 and you will see that the true result is 2005-04-25 02:54:09.253 which matches what you get from our new T-SQL solution.  

Just figured I would save you the time I toiled over this discrepancy, thinking I may be off by 12 hours, but couldn't figure out why my live test results with AD seemed accurate until I found that the PM was just a type-o.

Well that is pretty much it, but as usually I can't let you go without giving you something for your troubles.


HANDLING LOCAL TIMEZONE

Subsequently, here is a nice way to get the value in your own timezone, in addition to the bonus utility UDF found in Appendix:

DECLARE @timestamp BIGINT
                      SET @timestamp = 128881226539059241
                      ;
                      
                      DECLARE @tzOffset INT
                      SET @tzOffset = DATEDIFF(mi, GETUTCDATE(), GETDATE())
                      ;
                      
                      SELECT DATEADD(mi, @tzOffset, (@timestamp / 864000000000.0 - 109207))
                      ;

Select all Open in new window

Equally simple!

We calculate the difference of the universal date/time value to that of our local date/time since some time zones are both hour(s) and 30 minutes off GMT.


SUMMARY

In conclusion, we can now deal with converting Active Directory timestamps using just T-SQL code in Microsoft's SQL Server.  Please feel free to try this on older versions, but remember that user defined functions is a requirement to use our utility function; however, the in-line approach should work fine.  For SQL Server 2008 or higher, the new date and time data types may need to be taken into consideration, especially with respect to date 0 and other changes in upper and lower bounds of date/time values.

Hopefully this serves you well and thanks for reading.

Happy coding!

Best regards,


Kevin (aka MWVisa1)


Appendix A: Get Datetime From AD Timestamp Function.

-- =============================================
                      -- Author:      mwvisa1
                      -- Create date: 2009.05.29 22:35
                      -- Last modified: 2009.06.04 18:15
                      -- Description:      
                      --	Convert Active Directory (AD) 
                      --	based timestamps to SQL datetime.
                      -- =============================================
                      CREATE FUNCTION dbo.fn_GetDatetimeFromADTimestamp
                      (
                      -- AD timestamp to be converted
                      @timestamp BIGINT,
                      
                      -- flag determining if local time zone,
                      -- or GMT should be returned
                      @asLocalTime BIT = 0
                      )
                      RETURNS DATETIME
                      AS
                      BEGIN
                      	
                      -- variable to store resulting date
                      DECLARE @datetime DATETIME
                      
                      -- if local timezone flag set, 
                      -- then adjust offset minutes for timezone,
                      -- or 0 for GMT.
                      DECLARE @tzOffset INT
                      SET @tzOffset = 
                      	CASE COALESCE(@asLocalTime, 0)
                      		WHEN 1 THEN 
                      			DATEDIFF(mi, 
                      				GETUTCDATE(), 
                      				GETDATE()
                      			)
                      		ELSE 0
                      	END
                      ;
                      
                      -- calculate SQL number of days since date 0
                      /*
                          864000000000 (86400 * 10000000) 
                          is the number of 100ns intervals per day,
                          which is significant as AD timestamp is:
                          	the # of 100ns since 1601-01-01 00:00:00.000.
                         
                         :86400       == number of seconds per day
                         :10000000 == number of 100ns per second
                         
                          109207 (299 * 365 + 72) is:
                          	the offset # of days from 1601-01-01 (ANSI 0) 
                      	to 1900-01-01 (SQL 0).
                      
                         :299      == number of years between 1601 and 1900
                         :365      == standard days per year
                         :72       == number of leap years between 1601 and 1900 
                      */
                      DECLARE @timestampInDays FLOAT
                      SET @timestampInDays = @timestamp / 864000000000.0 - 109207 
                      ;
                       
                      -- -53690 is: # days from 1900-01-01 to 1753-01-01.
                      --	(i.e., datediff(d, 0, '1753-01-01'))
                      -- 1753-01-01 is minimum date in SQL Server 2005 or lower.
                      -- (limit of datetime data type)
                      -- try / catch not yet implemented in functions!!
                      IF @timestampInDays >= -53690
                      BEGIN
                         SET @datetime = DATEADD(mi, @tzOffset, @timestampInDays) 
                      END
                      ;
                      
                      -- return the result of the function      
                      RETURN @datetime;
                      
                      END

Select all Open in new window

The code should speak for itself and be relatively straight forward to follow; however, please leave comments below if you want further clarification or explanations.


Appendix B: Using Get Datetime From AD Timestamp Function.

DECLARE @timestamp BIGINT
                      DECLARE @asLocalTime BIT
                      SET @timestamp = 128881226539059241
                      SET @asLocalTime = 0
                      ;
                      
                      SELECT  
                         dbo.fn_GetDatetimeFromADTimestamp(
                            @timestamp, 
                            @asLocalTime
                         ) 
                      ;

Select all Open in new window

If all is well, you should see same results with parameters above:

2009-05-30 02:04:13.903

Select all Open in new window

With @asLocaltime set to 1, you will get results offset for your specific timezone.


References:
Date / Time Functions > http://msdn.microsoft.com/en-us/library/ms186724(SQL.90).aspx