Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Published:
Updated:
Browse All Articles > 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:
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}
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.
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.
*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:
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 DATETIMEASBEGIN-- variable to store resulting dateDECLARE @datetime DATETIME-- if local timezone flag set, -- then adjust offset minutes for timezone,-- or 0 for GMT.DECLARE @tzOffset INTSET @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 FLOATSET @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 >= -53690BEGIN SET @datetime = DATEADD(mi, @tzOffset, @timestampInDays) END;-- return the result of the function RETURN @datetime;END
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Sorry, ignore my request. I think this should work:
CREATE FUNCTION SQLDateTimeToADTimestamp
(
@datetime DATETIME
)
RETURNS BIGINT
AS
BEGIN
RETURN CAST(DATEDIFF(MINUTE, '1601-01-01 00:00:00', @datetime) AS BIGINT) * 600000000
END
GO
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.
Comments (2)
Commented:
Commented:
CREATE FUNCTION SQLDateTimeToADTimestamp
(
@datetime DATETIME
)
RETURNS BIGINT
AS
BEGIN
RETURN CAST(DATEDIFF(MINUTE, '1601-01-01 00:00:00', @datetime) AS BIGINT) * 600000000
END
GO