在SQL Server 2012中将整数值转换为DateTime [英] Convert integer value to DateTime in SQL Server 2012

问题描述

我有一个名为 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

错误提示：

从字符串转换日期和/或时间时转换失败。

Conversion failed when converting date and/or time from character string.

测试＃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:

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

Arithmetic overflow error converting expression to data type datetime.

我承认我从未见过像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?

推荐答案

几乎每次您看到以整数表示的日期/时间时，该数字都表示自已知纪元以来的时间流逝。这是 Unix时间的基础，简而言之，就是自此以来经过的秒数 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！