sql - 将列从 BigInt 转换为 DateTime
问题描述
我知道这已经在这里被问了很多,但是我尝试过的示例为我抛出了错误。
我有一张表正在从 AD 中提取用户数据。密码过期时间和密码上次设置时间有一栏。我检查了该列的数据类型,它是 BigInt。我正在尝试将其转换为实际的日期时间。这就是我正在运行的。
select
us.resourceid,
us.user_Name0,
us.displayName0,
us.department0,
us.msDS_UserPasswordExpiryTi,
us.pwdLastSet,
us.Windows_NT_Domain0
from v_R_User us
Where (us.User_Name0 not like '%$' and us.User_Name0 not like 'HealthMailbox%' and us.User_Name0 not like 'svc%')
这是返回数据的示例
resourceid user_Name0 displayName0 department0 msDS_UserPasswordExpiryTi pwdLastSet Windows_NT_Domain0
2063597569 ZZZZZ Name0 NULL 131113622747354070 130798262747354070 Dom1
2063597572 CCCCC Name1 Dept1 132781804234873444 132466444234873444 Dom1
2063597573 FFFFF Name2 Dept1 132829224102270283 132513864102270283 Dom1
2063597574 FFFFFE Name3 Dept1 132737776092485812 132422416092485812 Dom1
2063597575 YYYYYY Name4 Dept2 132781839502628544 132466479502628544 Dom2
2063597576 VVVVVV Name5 Dept3 132798423957001686 132483063957001686 Dom2
2063597577 GGGGG Name6 Dept3 132750906648479589 132435546648479589 Dom2
我尝试使用以下几行运行查询以创建另一列,并将数据转换为日期时间,但总是出现错误Arithmetic overflow error converting expression to data type int.
dateadd(s, convert(bigint, us.msDS_UserPasswordExpiryTi) / 10000, convert(datetime, '1-1-1970 00:00:00'))
dateadd(ms, us.msDS_UserPasswordExpiryTi / 86400000, (us.msDS_UserPasswordExpiryTi / 86400000) + 25567)
Cast(Dateadd(second, Cast(us.msDS_UserPasswordExpiryTi AS BIGINT) / 1000, '19700101') AS DATE)
cast(cast(us.msDS_UserPasswordExpiryTi as varchar(80)) as datetime)
我不确定它是数据问题还是什么。如果您只是将其中一个值复制出来并使用上述任何行仅使用该值运行 select 语句,我会得到相同的错误。等等
Select dateadd(s, convert(bigint, 131113622747354070) / 10000, convert(datetime, '1-1-1970 00:00:00'))
任何帮助表示赞赏。
解决方案
你的时间戳似乎是以亿秒为单位的——相当精确。
您可以将其分解为两种计算,一种用于日期,另一种用于日期部分。如果精确到一秒就足够了(正如您的问题所暗示的那样),那么:
Select dateadd(second, (131113622747354070 / 100000000) % (24 * 60 * 60),
dateadd(day, (131113622747354070 / 100000000) / (24 * 60 * 60), '1970-01-01'))