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.
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:
instead of this:
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);
When SQL Server does an implicit conversion from
numeric (or anything to
numeric, actually), it sets the default precision and scale to
numeric(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
SELECT ... ,CAST((CAST(accountExpires AS numeric(28,0)) / 864000000000.0 - 109207) AS DATETIME) ...
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.