SQL 将 Excel 日期序列号转换为常规日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13850605/
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 Excel Date Serial Number to Regular Date
提问by Sreedhar
I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date
我的 csv 文件中有一个名为 DateOfBirth 的列，其中包含 Excel 日期序列号日期
Example:
例子：
36464
37104
35412
When i formatted cells in excel these are converted as
当我在 excel 中格式化单元格时，它们被转换为
36464 => 1/11/1999
37104 => 1/08/2001
35412 => 13/12/1996
I need to do this transformation in SSIS or in SQL. How can this be achieved?
我需要在 SSIS 或 SQL 中进行此转换。如何做到这一点？
回答by Nick.McDermaid
In SQL:
在 SQL 中：
select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)
In SSIS, see here
在 SSIS 中，请参见此处
回答by Jacob Santiago
The marked answer is not working fine, please change the date to "1899-12-30" instead of "1899-12-31".
标记的答案不起作用，请将日期更改为“1899-12-30”而不是“1899-12-31”。
select dateadd(d,36464,'1899-12-30')
回答by Ricardo Souza
You can cast it to a SQL smalldatetime:
您可以将其转换为 SQL smalldatetime：
cast(41869 - 2 as smalldatetime)
SQL Server counts its dates from 01/01/1900 and Excel from 12/30/1899 = 2 days less.
SQL Server 从 01/01/1900 开始计算它的日期，从 12/30/1899 开始计算 Excel = 2 天。
回答by drinky
this actually worked for me
这实际上对我有用
dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-30')
(minus 1 more day in the date)
（日期减去 1 天）
referring to the negative commented post
参考负面评论帖子
回答by Hadi
SSIS Solution
SSIS 解决方案
The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE. Read more
DT_DATE 数据类型是使用 8 字节浮点数实现的。天数由整数增量表示，从 1899 年 12 月 30 日开始，午夜为时间零。小时值表示为数字小数部分的绝对值。但是，浮点值不能代表所有实数值；因此，可以在 DT_DATE 中显示的日期范围存在限制。阅读更多
From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATEColumn after converting it to a 8-byte floating-point number DT_R8.
从上面的描述中可以看出，在将这些值转换为 8 字节浮点数后，将它们映射到Column时，可以隐式转换这些值。DT_DATEDT_R8
Use a derived column transformation to convert this column to 8-byte floating-point number:
使用派生列转换将此列转换为 8 字节浮点数：
(DT_R8)[dateColumn]
Then map it to a DT_DATEcolumn
然后将其映射到一DT_DATE列
Or cast it twice:
或者施放两次：
(DT_DATE)(DT_R8)[dateColumn]
You can check my full answer here:
你可以在这里查看我的完整答案：
回答by hurleystylee
I had to take this to the next level because my Excel dates also had times, so I had values like this:
我不得不把它提升到一个新的水平，因为我的 Excel 日期也有时间，所以我有这样的值：
42039.46406 --> 02/04/2015 11:08 AM
42002.37709 --> 12/29/2014 09:03 AM
42032.61869 --> 01/28/2015 02:50 PM
(also, to complicate it a little more, my numeric value with decimal was saved as an NVARCHAR)
（另外，为了让它更复杂一点，我的十进制数值被保存为 NVARCHAR）
The SQL I used to make this conversion is:
我用来进行这种转换的 SQL 是：
SELECT DATEADD(SECOND, (
CONVERT(FLOAT, t.ColumnName) -
FLOOR(CONVERT(FLOAT, t.ColumnName))
) * 86400,
DATEADD(DAY, CONVERT(FLOAT, t.ColumnName), '1899-12-30')
)
回答by Tim Lehner
Excel stores datetimes as a floating point numberthat represents elapsed time since the beginning of the 20th century, and SQL Server can readily cast between floatsand datetimes in the same manner. The difference between Excel and SQL server's conversion of this number to datetimes is 2 days (as of 1900-03-01, that is). Using a literal of 2efor this difference informs SQL Server to implicitly convert other datatypes to floats for very input-friendly and simple queries:
Excel 将日期时间存储为表示自 20 世纪初以来经过的时间的浮点数，并且 SQL Server 可以很容易地以相同的方式在浮点数和日期时间之间进行转换。Excel 和 SQL 服务器将此数字转换为日期时间之间的差异是 2 天（截至 1900-03-01，即）。2e对于这种差异，使用字面量会通知 SQL Server 将其他数据类型隐式转换为浮点数，以实现对输入友好且简单的查询：
select
cast('43861.875433912' - 2e as datetime) as ExcelToSql, -- even varchar works!
cast(cast('2020-01-31 21:00:37.490' as datetime) + 2e as float) as SqlToExcel
-- Results:
-- ExcelToSql SqlToExcel
-- 2020-01-31 21:00:37.490 43861.875433912
回答by Lankymart
Found this topic helpful so much so created a quick SQL UDF for it.
发现这个主题非常有用，因此为它创建了一个快速的 SQL UDF。
CREATE FUNCTION dbo.ConvertExcelSerialDateToSQL
(
@serial INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dt AS DATETIME
SELECT @dt =
CASE
WHEN @serial is not null THEN CAST(@serial - 2 AS DATETIME)
ELSE NULL
END
RETURN @dt
END
GO
回答by Nicolaesse
In addition of @Nick.McDermaid answer I would like to post this solution, which convert not only the day but also the hours, minutes and seconds:
除了@Nick.McDermaid 的回答之外，我还想发布这个解决方案，它不仅可以转换日期，还可以转换小时、分钟和秒：
SELECT DATEADD(s, (42948.123 - FLOOR(42948.123))*3600*24, dateadd(d, FLOOR(42948.123),'1899-12-30'))
For example
例如
42948.123to2017-08-01 02:57:07.00042818.7166666667to2017-03-24 17:12:00.000
42948.123到2017-08-01 02:57:07.00042818.7166666667到2017-03-24 17:12:00.000
回答by Selcuk Akbas
Google BigQuery solution
Google BigQuery 解决方案
Standard SQL
标准 SQL
Select Date, DATETIME_ADD(DATETIME(xy, xm, xd, 0, 0, 0), INTERVAL xonlyseconds SECOND) xaxsa
from (
Select Date, EXTRACT(YEAR FROM xonlydate) xy, EXTRACT(MONTH FROM xonlydate) xm, EXTRACT(DAY FROM xonlydate) xd, xonlyseconds
From (
Select Date
, DATE_ADD(DATE '1899-12-30', INTERVAL cast(FLOOR(cast(Date as FLOAT64)) as INT64) DAY ) xonlydate
, cast(FLOOR( ( cast(Date as FLOAT64) - cast(FLOOR( cast(Date as FLOAT64)) as INT64) ) * 86400 ) as INT64) xonlyseconds
FROM (Select '43168.682974537034' Date) -- 09.03.2018 16:23:28
) xx1
)
