使用T-SQL转换AD时间戳

2024-05-18 • 问答

如何将Windows NT时间从SQL查询请求转换为可读格式？我正在做用户帐户的广告提取，我想将Windows AD时间戳转换或转换为更好的可读格式。问题是我在进行拉动时无法进行转换。

查询：

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,mail,accountExpires
        from ''LDAP PATH''
        where objectcategory=''person'' and objectclass = ''user'' and name=''*'' '
    );

我的查询返回Windows NT时间的accountExpires字段，但我希望它是这样的：

2020-02-09 15：23：36.367

代替此：

132257354163700000

我想出了一个简单的解决方案，一个接一个地进行转换，但是我希望它在下拉时执行CAST，而不必为每个用户都进行

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

当SQL Server从nvarchar到numeric（实际上是到numeric的{{3}}）时，它将设置默认精度并缩放到numeric(18,0)。对于您的问题中的示例数据而言，这已经足够好了，但是错误消息表明您的数据集中某处的值超过了默认数据类型的容量。

要克服这一点，请尝试将显示强制转换为更大容量numeric。也许：

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

numeric(19,0)可能就足够了，但是如果您超过19，那么您最好也转到28，因为20-28的存储容量都相同。

这对我在 AD 中的每个用户都有效。

CAST((convert(bigint,lastlogontimestamp) / 864000000000.0 - 109207) AS DATETIME) as lastLogonTimestamp,

--克服datetime转大或小转null的限制当 convert(bigint,accountexpires) = 0 然后为空时的情况当转换（bigint，accountexpires）> 2650467743999999716 then null else CAST((convert(bigint,accountexpires) / 864000000000.0 - 109207) AS DATETIME) end as accountexpires

使用T-SQL转换AD时间戳

milu4ever 回答：使用T-SQL转换AD时间戳

大家都在问