Active Directory -> SQL (Convert)

Question

Active Directory -> SQL (Convert)

kd11

Hall of Fame

Points: 3447
More actions
February 24, 2017 at 7:18 am

#321979

I have extract certain data/fields from Active Directory into a table (sql) and some of the columns are such as AccountExpires, PWDLASTSET, ETC have a these long numeric strings which I'm sure are dates but when I try to Convert(datetime,field1,120) the fields to a readable format I receive: "Arithmetic overflow error converting expression to data type datetime." . Does anyone know how I can change 127777589408075800000 into January 1 20117 (<- That's just an example) ? Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I found a web page with a solution which appears to work, although you may have to re-examine the value you posted. I had to drop 3 zeroes from the end of the string of numbers to get something in range for SQL Server. Here's the code:
SELECT T.binary_value , D.DATE_VALUE , DO.DATE_CONVERTED FROM ( VALUES (127777589408075800) ) AS T(binary_value) CROSS APPLY ( SELECT (CAST(T.binary_value AS bigint) / (864000000000.0)) - 109207 AS DATE_VALUE ) AS D CROSS APPLY ( SELECT DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()), D.DATE_VALUE) AS DATE_CONVERTED ) AS DO

Be aware that just dropping 2 zeroes from that value put it in the date range of year 5650. Not exactly a realistic date value to appear in Active Directory, much less SQL Server. The web page I found is here:

https://www.experts-exchange.com/articles/811/Converting-Active-Directory-Timestamps-in-Microsoft-SQL-Server.html

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323518 More actions · Answer 2

i know power shell has a function FromFileTime that can convert that wierd value( #ticks?) to datetime

i've sued this expression:
Expression={[DATETIME]::fromFileTime($_.accountExpires)}}

$Results = Get-ADUser -Filter * -ResultPageSize 100 | Get-ADObject -Properties * | select -property sAMAccountName,ou, GivenName,SurName,DisplayName,email,emailaddress, StreetAddress,City,State,PostalCode, HomePhone,MobilePhone,OfficePhone,Fax, Company,Organization,Department,Title,Description,Office, extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5, @{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},Enabled,PasswordLastSet, @{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}},PasswordNeverExpires,PasswordExpired, LastLogonDate,whenCreated

$Results | Out-GridView

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgmunson SSC Guru Points: 110640 More actions · Answer 3

kd11 - Friday, February 24, 2017 7:18 AM
I have extract certain data/fields from Active Directory into a table (sql) and some of the columns are such as AccountExpires, PWDLASTSET, ETC have a these long numeric strings which I'm sure are dates but when I try to Convert(datetime,field1,120) the fields to a readable format I receive: "Arithmetic overflow error converting expression to data type datetime." . Does anyone know how I can change 127777589408075800000 into January 1 20117 (<- That's just an example) ? Thank you.

Ya know, I just realized that your text may NOT have been a typo. Given the value you have, you'll be entirely out of range if you expect SQL Server to give you any date beyond 9999-12-31 23:59:59.997 for a datetime datatype, and only a few milliseconds later for the more precise datetime data types.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 4

Referencing the following URL ...
https://msdn.microsoft.com/en-us/library/ms675098(v=vs.85).aspx
... and if you only want the date and not the time, you can do the following...
DECLARE @AcctExpires VARCHAR(32) = '127777589408075800000' ,@BaseDate DATE = '16010101' ,@DTValue DATE ; SELECT DATEADD(dd,CONVERT(BIGINT,LEFT(@AcctExpires,LEN(@AcctExpires)-7))/86400000.0,@BaseDate) ; /* Results ConvertedDT ----------- 2005-11-29 */

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kd11 Hall of Fame Points: 3447 More actions · Answer 5

Thanks guys just doing this works "

CAST((pwdlastset / 864000000000.0 - 109207) AS DATETIME

)"

but When I try to do it for a different column(with a nvarchar(256) datatype) it would not work. The value for the second column is always "9223372036854775807" so I tried doing this

CONVERT

(nvarchar(16),convert(Datetime, column2,103),120) And I get the Conversion failed when converting date and/or time from character string Error message. Do I need to alter the table column from nvarchar to datetime.

sgmunson SSC Guru Points: 110640 More actions · Answer 6

kd11 - Monday, February 27, 2017 10:21 AM
Thanks guys just doing this works "
CAST((pwdlastset / 864000000000.0 - 109207) AS DATETIME
)"
but When I try to do it for a different column(with a nvarchar(256) datatype) it would not work. The value for the second column is always "9223372036854775807" so I tried doing this
CONVERT
(nvarchar(16),convert(Datetime, column2,103),120) And I get the Conversion failed when converting date and/or time from character string Error message. Do I need to alter the table column from nvarchar to datetime.

Try to convert from nvarchar to bigint first, then do the math. If that fails, try decimal(19,0) instead.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)