February 24, 2017 at 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.
February 24, 2017 at 2:36 pm
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:
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 24, 2017 at 2:56 pm
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
February 24, 2017 at 3:04 pm
kd11 - Friday, February 24, 2017 7:18 AMI 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 24, 2017 at 8:28 pm
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
February 27, 2017 at 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.
February 28, 2017 at 7:03 pm
kd11 - Monday, February 27, 2017 10:21 AMThanks 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply