MSSQL - 将自1970年以来的毫秒转换为datetime2 [英] MSSQL - Convert milliseconds since 1970 to datetime2
本文介绍了MSSQL - 将自1970年以来的毫秒转换为datetime2的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
考虑以下查询(在MSSQL 2008中):
Consider the following query (in MSSQL 2008):
SELECT dateModified FROM SomeTable;
这将以JavaScript格式返回浮点数(自1970年以来的毫秒):
This returns floats in javascript format (milliseconds since 1970):
dateModified
============
1301598290687
1071003581343
1311951478593
如何将它转换为select?中的datetime2右边?
How can I convert this to a datetime2 right in the select?
推荐答案
我将把float转换为bigint,然后创建datetime:
I would convert the float to a bigint and then create the datetime:
select
DATEADD(MILLISECOND,
cast(dateModified as bigint) % 1000,
DATEADD(SECOND, cast(dateModified as bigint) / 1000, '19700101'))
from sometable
a href =http://sqlfiddle.com/#!3/ed339/3> SQL Fiddle with Demo
See SQL Fiddle with Demo
这篇关于MSSQL - 将自1970年以来的毫秒转换为datetime2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文