Converting AD TimeStamp with T-SQL

Question

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);

Answer 1

When SQL Server does an implicit conversion from nvarchar to 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 numeric. Maybe:

SELECT
  ...
 ,CAST((CAST(accountExpires AS numeric(28,0)) / 864000000000.0 - 109207) AS DATETIME)
 ...

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.