如何将数字转换为数据时间 [英] How to convert numeric number into datatime

问题描述

我需要使用SQL Server将bigint值转换为datetime值的帮助.
示例值634254092190100000

I need help to convert a bigint value into a datetime value using SQL Server.
Example value 634254092190100000

如何转换为日期时间值?

How to convert to datetime value?

到目前为止，这是我所拥有的，但是出现错误:

So far this is what I have but getting an error:

将表达式转换为int数据类型的算术溢出错误

Arithmetic overflow error converting expression to data type int

select dateadd(s, convert(bigint, 632979854880200000) / 1000, convert(datetime, '1-1-1970 00:00:00'))

推荐答案

请执行以下操作:

SELECT CAST((YOURVALUE- 599266080000000000) / 864000000000 AS datetime)

例如:

SELECT CAST((635307578922100000 - 599266080000000000) / 864000000000 AS datetime)

给予"2014-03-18 16:44:52"

知道:
-599266080000000000是19000101
-864000000000是毫秒/天的数量
-599266080000000000/864000000000 = 693595/365 = 1900(aprox)

Knowing that :
- 599266080000000000 is 19000101
- 864000000000 is the number of ms / day
- 599266080000000000 / 864000000000 = 693595 /365 = 1900 (aprox)

请参阅 https://docs.microsoft.com/fr-fr/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql 有关每个日期时间类型族的开始日期的更多详细信息...

Please see https://docs.microsoft.com/fr-fr/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql for further details about starting day for each datetime type family...

这篇关于如何将数字转换为数据时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！