How to convert Windows NT time from a SQL query pull to a readable format? I'm doing an AD pull of user accounts and I want to convert or CAST the windows AD timestamp to a better readable format. The issue is I am having problems doing that converting when doing the pull.
Query:
SELECT
CASE
WHEN CHARINDEX('@', userPrincipalName) > 7
THEN SUBSTRING(userPrincipalName, 1, (CHARINDEX('@', userPrincipalName)) - 1)
ELSE ''
END AS edipi
,UPPER(samaccountname) AS samaccountname
,givenName AS firstName
,sn AS lastName
,initials
,UPPER(mail) AS email
,userAccountControl
,telephoneNumber
,title
,accountExpires
FROM
OPENQUERY
(ADSI
,'select
givenName,
samaccountName,
userPrincipalName,
sn,
initials,
mail,
userAccountControl,
telephoneNumber,
title,
accountExpires
from ''LDAP PATH''
where objectcategory=''person'' and objectclass = ''user'' and name=''*'' '
);My query returns the accountExpires Field in windows NT time but, I want it to be something like this:
2020-02-09 15:23:36.367
instead of this:
132257354163700000
I have come up with a simple solution doing the converting one by one, but I want it to do the CAST on the pull instead of having to do this for every users
DECLARE @accountExpired BIGINT
SET @accountExpired = 132257354163700000; --This is a random time pulled from a user from the above select statement.
SELECT CAST((@accountExpired / 864000000000.0 - 109207) AS DATETIME);
3条答案
按热度按时间4szc88ey1#
When SQL Server does an implicit conversion from
nvarchartonumeric(or anything tonumeric, actually), it sets the default precision and scale tonumeric(18,0). That's good enough for the sample data in your question, but the error message indicates that there's a value somewhere in your data set that exceeds the capacity of the defaulted data type.To get past that, try an explicit cast to a larger capacity
numeric. Maybe:Probably,
numeric(19,0)would be sufficient, but if you go over 19, you might as well go to 28 since 20-28 all have the same storage size.o7jaxewo2#
This worked for me for every user in AD.
-- To overcome the limit of datetime convert to big or to small to null case when convert(bigint, accountexpires) = 0 then null when convert(bigint, accountexpires) > 2650467743999999716 then null else CAST((convert(bigint, accountexpires) / 864000000000.0 - 109207) AS DATETIME) end as accountexpires
jmo0nnb33#
Several years late to the party, but hopefully this helps others trying to query LDAP.
Erics' answer is great! However, I ran into the overflow error and had to increase the NUMERIC(28,0) to NUMERIC(38,0) to account for very large numbers/dates from AD. The Select statement example below will output "2023-07-15 00:00:00.000"
Below is a working example, on my domain of course, that pulls 500 records from LDAP and converts the accountExpires to a readable date. The userAccountControl statements are for pulling Active and Disabled employees only and excluding accounts without a password.
I hope this helps