问题描述
我有一个名为 EventLog的表，该表具有名为 nDateTime 的列，其类型为 int 。
I have a table called "EventLog" which has the column called nDateTime of type int.
这是具有某些值的表 EventLog ：
This is the table "EventLog" with some values:
-----------------
| nDateTime |
-----------------
| 978307200 |
-----------------
| 978307219 |
-----------------
| 978513562 |
-----------------
| 978516233 |
-----------------
| 978544196 |
-----------------
| 1450379547 |
-----------------
| 1472299563 |
-----------------
| 1472299581 |
-----------------
| 1472300635 |
-----------------
| 1472300644 |
-----------------
| 1472300673 |
-----------------
我需要获取DateTime值，并尝试了以下语句，但收到以下错误：
I need to get the DateTime value, and I tried the following statements, but I receive these errors:
测试＃1：
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), nDateTime), 103) AS 'Formatted date'
FROM EventLog
错误提示：
测试＃2：从修改：
SELECT CONVERT(DATETIME, nDateTime, 103) AS 'Formatted date'
FROM EventLog
然后测试＃3 进行：
SELECT CAST(nDateTime AS datetime) AS 'Formatted date'
FROM EventLog
不能回答我的问题，因为（测试＃2和测试＃3）都生成此错误：
The duplicate question doesn't answer my question because (both, test #2 and test #3) generates this error:
我承认我从未见过像Date这样的值，为此，我
I admit that I never saw such value as a Date, and for that, I'm kind of confused in how to proceed.
我的问题是：如何从样本数据中获取有效的DateTime值？
My question is: How can get the valid DateTime value from the sample data?
推荐答案
几乎每次您看到以整数表示的日期/时间时，该数字都表示自已知纪元以来的时间流逝。这是的基础，简而言之，就是自此以来经过的秒数 1970年1月1日00:00:00
Almost every time you see a date/time represented as an integer, that number represents the passage of time since a known epoch. This is the basis of Unix time which is, put simply, the number of seconds which have elapsed since 1st January 1970 00:00:00
使用此方法，我们可以检查您提供的某些值
Using this, we can check with some values you have provided
declare @dt DATETIME = '1970-01-01' -- epoch start
print dateadd(second,978307200,@dt ) -- Jan 1 2001 12:00AM
print dateadd(second,1472300673,@dt ) -- Aug 27 2016 12:24PM
似乎有可能，但谁知道呢！!
Seems possible, but who knows?!
您只需使用
declare @dt DATETIME = '1970-01-01' -- epoch start
SELECT
nDateTime AS OriginalData,
DATEADD(second, nDateTime,@dt) AS ActualDateTime
FROM EventLog
这篇关于在SQL Server 2012中将整数值转换为DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！