在 SQL Server 中将秒转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9031160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert seconds to datetime in SQL Server
提问by Ventsislav Marinov
How to convert seconds to datetime? I try this, but results are not correct:
如何将秒转换为datetime？我试试这个，但结果不正确：
CONVERT(datetime, DATEADD(ms, dateTimeInSeconds, 0))
Here is an example: 1900-01-15 21:58:16.287it's should be something like this 2010-11-02 14:56:50.997
这是一个例子：1900-01-15 21:58:16.287它应该是这样的2010-11-02 14:56:50.997
回答by aF.
Given your example, try this:
鉴于你的例子，试试这个：
select DATEADD(s, dateTimeInMilliseconds, '19700101')
回答by Carlos Bercero
Informative time span string:
信息时间跨度字符串：
declare @seconds int = 93825
convert(varchar,(@seconds/86400)) + 'd:' + format(dateadd(ss,@seconds,0),'HH\h:mm\m:ss\s')
Result: 1d:02h:03m:45s
结果：1d:02h:03m:45s
回答by Guffa
When you use the value zero for date, this is converted to 1900-01-01. Use the specific date that you have selected as epoch:
当您使用零值作为日期时，这将转换为1900-01-01. 使用您选择的特定日期作为纪元：
convert(datetime, dateadd(ms, dateTimeInMilliseconds, '2010-01-01'))
Note that the datetimedata type doesn't have millisecond precision, the resolution is 1/300 second. If you for example have four milliseconds and convert it, you get 2010-01-01 00:00:00.003rather than 2010-01-01 00:00:00.004. If you need to preserve the millisecond resolution, you need to use the datetime2data type:
请注意，datetime数据类型没有毫秒精度，分辨率为 1/300 秒。例如，如果您有四毫秒并对其进行转换，您将得到2010-01-01 00:00:00.003而不是2010-01-01 00:00:00.004. 如果需要保留毫秒分辨率，则需要使用datetime2数据类型：
convert(datetime2, dateadd(ms, dateTimeInMilliseconds, cast('2010-01-01' as datetime2)))
Edit:
编辑：
To use seconds instead of milliseconds, use sinstead of msin the dateaddcall:
要使用秒而不是毫秒，请在调用中使用s而不是：msdateadd
convert(datetime, dateadd(ms, dateTimeInSeconds, '1970-01-01'))
