INTEGER8...
It's possible you're pulling a value from Active Directory for discovery like LastLogonTimeStamp which stores it's value as something known as Integer8 (http://www.rlmueller.net/Integer8Attributes.htm). If that's the case, you may notice the values you've got saved in SQL aren't perhaps what you were expecting:
128462210724001300
Fields within AD that have integer8 numbers like this include
- accountExpires
- badPasswordTime
- lastLogon
- lockoutTime
- maxStorage
- pwdLastSet
- uSNChanged
- uSNCreated
- lockoutDuration
- lockoutObservationWindow
- maxPwdAge
- minPwdAge
- modifiedCount
So...what to do with this odd string of digits? It doesn't look like a date, you can't just CONVERT() it to a datetime value directly and it doesn't look like it can be picked apart visually. Well, if you read up on that link above, you'll see a solution there in VBScript. Well, that's not going to fly for us, so why don't we convert it to T-SQL. I've also seen a few SQL functions out there that some people have written, but they all seem to either make things way too complicated, or get something just a little wrong. So instead of copying something from the internet that we don't understand or that may be wrong, let's just use the information we know about integer8 values to figure it out.
BACKGROUND
Alright, unless you understand it better, the solution just isn't going to make sense. So let me just give a quick background before we get into code to do the conversion.
Integer8. So integer8 is this 64-bit (8-byte) integer that is the cornerstone of certain date fields in Active Directory (like listed above). It represents the number of 100-nanosecond intervals that have elapsed since 1/1/1601. A nanosecond is 1 billionth of a second (so 1 billion of them to a second), which means there are 10 Million of these 100-nanosecond intervals in a second and 60 Million of them in a minute. So, if we figure out how many minutes an integer8 number represents, we can figure out what date is that many minutes from 1/1/1601.
One of the things we have to work around in T-SQL is the fact that in SQL 2005, the DATE* functions use a "Base Date" of 1/1/1900, meaning that's as far back as it will go. So you can't calculate how many minutes have elapsed since 1/1/1601, you have to do it from 1/1/1900. Which means you have to subtract out the minutes from 1/1/1601 to 1/1/1900 first. This isn't an issue in SQL 2008 because it supports the DATETIME2 data type which goes from 1/1/0001 to 12/31/9999.
Perhaps we should just get to the steps so it makes more sense:
STEPS CONVERT INTEGER8 TO DATETIME
Using the above link as a guide, here are the steps you need to perform:
FOR SQL 2000
- Buy SQL 2008 or higher and follow those instructions...seriously...
FOR SQL 2005
- Given an integer8 date, figure out how many minutes elapsed that it represents
(Divide the number by 60 Million)
- Calculate a "Base Date" offset--the number of minutes-elapsed between 1/1/1601 and 1/1/1900
(157258080)
- Calculate the timezone bias so the output is in local time
- Calculate the new number of minutes that have elapsed since the base date given the timezone bias
(take the minutes elapsed, subtract the base-date offset and add the timezone bias)
- Add that number of minutes to the base-date and determine the resultant date
FOR SQL 2008
Because SQL 2008 has a datetime2 datatype, it can go all the way back to 1/1/1601 and perform date functions on it so the "Base Date" step isn't necessary.
- Given an integer8 date, figure out how many minutes-elapsed that it represents
(Divide the number by 60 Million)
- Calculate the timezone bias so the output is in local time
- Calculate the new number of minutes that have elapsed since 1/1/1601 given the timezone bias
(take the minutes elapsed, subtract the base-date offset and add the timezone bias)
- Add that number of minutes to 1/1/1601 and determine the resultant date
NOW, THE T-SQL
SQL 2005
----------------------------
-- FULL, STEP-BY-STEP METHOD
----------------------------
-- Don't worry, this is step-by-step to show the logic. It can all be put into
-- one statement or a function like we've done below
-- TAKE AN INTEGER8, WHICH IS THE NUMBER OF 100-NANOSECOND INTERVALS SINCE 12:00AM 1/1/1601
DECLARE @Int8 BIGINT
SET @Int8 = 128462210724001300
-- AND FIGURE OUT HOW MANY MINUTES THAT IS...
-- THERE ARE 1 BILLION NANOSECONDS IN A SECOND, THUS 10MILLION 100-NANOSECOND INTERVALS
-- AND 60 TIMES THAT PER MINUTE SO INT8 DIVIDED BY (60 * 10MILLION) IS THE NUMBER OF
-- MINUTES THAT HAVE ELAPSED SINCE 12:00AM 1/1/1601
DECLARE @MinutesElapsed BIGINT
SET @MinutesElapsed = @Int8 / 600000000
-- NOW, BECAUSE THE DATEADD FN USES A "BASE DATE" OF 1/1/1900, WE NEED TO TAKE THE MINUTES
-- ABOVE AND SUBTRACT NUMBER OF MINUTES THAT HAVE ELAPSED BETWEEN 1/1/1601 TO 1/1/1900
DECLARE @BaseDateOffset BIGINT;
SET @BaseDateOffset = 157258080
-- NOW WE CALCULATE THE OFFSET FROM GMT/UTC THAT OUR TIMEZONE GIVES US SO THE OUTPUT IS IN LOCAL TIME
declare @TimezoneBias INT;
SET @TimezoneBias = DATEDIFF(Minute,GetUTCDate(),GetDate())
-- LASTLY, LET'S TAKE THE NUMBER OF MINUTES ELAPSED, WITH THE BASE-DATE OFFSET AND THE LOCAL TIME BIAS
-- AND FIGURE OUT WHAT END-DATE THAT GIVES US
SELECT DATEADD(mi,@MinutesElapsed - @BaseDateOffset + @TimezoneBias,0)
GO
-----------------------------------------
-- OR TO PUT ALL OF THAT IN ONE STATEMENT
-----------------------------------------
DECLARE @Int8 BIGINT
SET @Int8 = 128462210724001300
SELECT DATEADD(mi,(@Int8 / 600000000) - 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0)
GO
----------------------------
-- OR PUT IT INTO A FUNCTION
----------------------------
CREATE FUNCTION dbo.udf_Int8_to_DateTime(
@Int8 BIGINT
)
RETURNS DATETIME
AS
BEGIN
RETURN (DATEADD(mi,(@Int8 / 600000000) - 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0))
END
GO
-- AND THEN CALL IT
SELECT dbo.udf_Int8_to_DateTime(128462210724001300)
GO
SQL 2008
Like we said earlier, SQL 2008 doesn't need one of the steps because it can handle DATETIME2 and do date math back to 1/1/1601, as long as you CAST or CONVERT the data to a DATETIME2 in the DATEADD as you see below.
----------------------------
-- FULL, STEP-BY-STEP METHOD
----------------------------
-- Don't worry, this is step-by-step to show the logic. It can all be put into
-- one statement or a function like we've done below. Keep reading.
-- TAKE AN INTEGER8, WHICH IS THE NUMBER OF 100-NANOSECOND INTERVALS SINCE 12:00AM 1/1/1601
DECLARE @Int8 BIGINT
SET @Int8 = 128462210724001300
-- AND FIGURE OUT HOW MANY MINUTES THAT IS...
-- THERE ARE 1 BILLION NANOSECONDS IN A SECOND, THUS 10MILLION 100-NANOSECOND INTERVALS
-- AND 60 TIMES THAT PER MINUTE SO INT8 DIVIDED BY (60 * 10MILLION) IS THE NUMBER OF
-- MINUTES THAT HAVE ELAPSED SINCE 12:00AM 1/1/1601
DECLARE @MinutesElapsed BIGINT
SET @MinutesElapsed = @Int8 / 600000000
SELECT @MinutesElapsed
-- NOW WE CALCULATE THE OFFSET FROM GMT/UTC THAT OUR TIMEZONE GIVES US SO THE OUTPUT IS IN LOCAL TIME
declare @TimezoneBias INT;
SET @TimezoneBias = DATEDIFF(Minute,GetUTCDate(),GetDate())
-- LASTLY, LET'S TAKE THE NUMBER OF MINUTES ELAPSED SINCE 1/1/1601, WITH THE LOCAL TIME BIAS
-- AND FIGURE OUT WHAT END-DATE THAT GIVES US. NEED TO CAST/CONVERT THE DATE TO DATETIME2 IN
-- ORDER TO ADD MINUTES FROM ALL THE WAY BACK ON 1/1/1601, OTHERWISE IT ASSUMES DATETIME WHICH
-- ONLY GOES BACK TO 1/1/1900.
SELECT DATEADD(mi,@MinutesElapsed + @TimezoneBias,CAST('1/1/1601' AS DATETIME2))
GO
-----------------------------------------
-- OR TO PUT ALL OF THAT IN ONE STATEMENT
-----------------------------------------
DECLARE @Int8 BIGINT
SET @Int8 = 128462210724001300
SELECT DATEADD(mi,(@Int8 / 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2))
GO
----------------------------
-- OR PUT IT INTO A FUNCTION
----------------------------
CREATE FUNCTION dbo.udf_Int8_to_DateTime(
@Int8 BIGINT
)
RETURNS DATETIME2
AS
BEGIN
RETURN (DATEADD(mi,@Int8 / 600000000 + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2)))
END
GO
-- AND THEN CALL IT
SELECT dbo.udf_Int8_to_DateTime(128462210724001300)
GO
SUMMARY
So that's really it. It's all about converting the 100-nanosecond intervals to minutes and doing some math. So we've got TSQL that will convert an Integer8 value (from Active Directory) into a DateTime/DateTime2 value.
Some notes:
- If you want it in UTC/GMT, then you can just skip the TimezoneBias part.
- This may not be perfect, I don't know how all of the leap seconds that have been added over the years play into this...do the DATEDIFF/DATEADD functions handle that? Dunno.
- The number argument of the DATEADD function cannot exceed the range of INT (2147483647) so if the INTEGER8 number is obscenely huge (spans more than 4084 years, 24 days, 2 hours & 7 minutes) then you'll likely get an overflow error. But we shouldn't hit that for more than a thousand years, right?
This has been tested on SQL 2005 and SQL 2008, but you should always test on lab systems before moving any code to production. This is released "AS IS" and confers no rights.
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
