<

Converting Active Directory Timestamps in Microsoft SQL Server

Published on
30,383 Points
20,683 Views
2 Endorsements
Last Modified:
Awarded
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:

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:
DECLARE @timestamp BIGINT
SET @timestamp = 128881226539059241
;

SELECT CAST((@timestamp / 864000000000.0 - 109207) AS DATETIME)
;

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

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#

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

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

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

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.


References:
Date / Time Functions > http://msdn.microsoft.com/en-us/library/ms186724(SQL.90).aspx
2
Comment
Author:Kevin Cross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month