在SQLServer中将bigint转换为日期时间 [英] Converting bigint to datetime in sqlserver

问题描述

我正在尝试将bigint转换为日期和时间.我已经使用以下链接作为指导，但是它不能像预期的那样起作用，因为根据我对该解决方案的理解，它基于毫秒.

I am trying to convert my bigint into a date and time. I have used the following link as a guidedance but it is not working as it is suppose to because from my understanding in this solution it is based on a millesecond.

将bigint转换为日期时间

我具有以下编码，并尝试了以下操作:

I have the following coding and have tried the following:

declare @starttime as bigint;
set @starttime = '2021021209295600000';

Select dateadd(HOUR, (@starttime / 100000) % (24 * 60 * 60),
               dateadd(day, (@starttime / 100000) / (24 * 60 * 60), '1970-01-01'))

系统提示以下错误:

The data types time and datetime are incompatible in the add operator. 

也如前所述，我已经尝试了上述解决方案，但是给出了以下输出，该输出是不正确的 select dateadd(s，convert(bigint，@starttime)/1000，convert(datetime，'1-1-1970 00:00:00'))

Also as mentioned earlier I have tried the above mentioned solution however the following output is given which is incorrect select dateadd(s, convert(bigint, @starttime) / 1000, convert(datetime, '1-1-1970 00:00:00'))

有人可以帮我吗?期望值是 2021-02-12 09:29:56

推荐答案

这不是很漂亮，但是我会将它 CONVERT 转换为 varchar ，然后注入一些所需的字符，然后将 CONVERT 转换为 datetime (因为您有5个小数位，我认为它实际上是 datetime2(5)):

This isn't pretty, but I would CONVERT it to a varchar, inject some of the needed characters, and then CONVERT to a datetime (as you have 5 decimal places, I assume it's actually meant to be a datetime2(5)):

DECLARE @starttime as bigint;
SET @starttime = 2021021209295600000;

SELECT CONVERT(datetime2(5),STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(CONVERT(varchar(20),@starttime),15,0,'.'),13,0,':'),11,0,':'),9,0,'T'),7,0,'-'),5,0,'-'));

这篇关于在SQLServer中将bigint转换为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！