Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Converting Active Directory Timestamps in Microsoft SQL Server

Kevin CrossChief Technology Officer
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you!


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...


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:

ANSI and, ultimately, AD's date 0 is 1601-01-01 00:00:00.000 or January 1st, 1601 at midnight.
The datetime data type in SQL 2005 and below has date 0 of 1900-01-01 00:00:00.000 or January 1st, 1900 at midnight.
AD timestamps are calculated as the number of 100 nanoseconds from ANSI date 0.
An integer divided by another integer in T-SQL results in an integer; decimal remainder is lost.

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}

Open in new window

Quick In-line Solution:
Given, the last bullet above, the formula looks as follows in T-SQL:
                      SET @timestamp = 128881226539059241
                      SELECT CAST((@timestamp / 864000000000.0 - 109207) AS DATETIME)

Open in new window


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

Open in new window


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.

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#

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.


Subsequently, here is a nice way to get the value in your own timezone, in addition to the bonus utility UDF found in Appendix:
                      SET @timestamp = 128881226539059241
                      DECLARE @tzOffset INT
                      SET @tzOffset = DATEDIFF(mi, GETUTCDATE(), GETDATE())
                      SELECT DATEADD(mi, @tzOffset, (@timestamp / 864000000000.0 - 109207))

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.


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
                      -- 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 
                      		ELSE 0
                      -- 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
                         SET @datetime = DATEADD(mi, @tzOffset, @timestampInDays) 
                      -- return the result of the function      
                      RETURN @datetime;

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 @asLocalTime BIT
                      SET @timestamp = 128881226539059241
                      SET @asLocalTime = 0

Open in new window

If all is well, you should see same results with parameters above:
2009-05-30 02:04:13.903

Open in new window

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

Date / Time Functions >
Kevin CrossChief Technology Officer
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Comments (2)

Hi, is there a function to convert date time to Active Directory Timestamps?
Sorry, ignore my request. I think this should work:

      @datetime DATETIME
      RETURN CAST(DATEDIFF(MINUTE, '1601-01-01 00:00:00', @datetime) AS BIGINT) * 600000000

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.