将Excel日期序列号转换为普通日期

Question

我有一个称为 DateOfBirth 的列在我的 CSV 文件中，其中包含 Excel 日期序列号日期。

示例：

  36464
  37104
  35412

当我在 Excel 中格式化单元格时，它们会被转换为

  36464 => 1/11/1999
  37104 => 1/08/2001
  35412 => 13/12/1996

我需要在SSIS或SQL中执行这个转换操作。如何实现？

- Sreedhar

---

顺便说一下，那些不是儒略日，它们是Excel日期序列号。35464对应的是公元前4614年11月1日12:00:00.0 UT 周二的儒略日和公元1958年9月18日00:00:00.0 UT 周四的修正儒略日。这里有一个关于儒略日的解释链接：http://aa.usno.navy.mil/data/docs/JulianDate.php。 - William Salzman

Answer 1

在 SQL 中：

select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

在SSIS中，请参见此处：

http://msdn.microsoft.com/zh-cn/library/ms141719.aspx

- Nick.McDermaid

Answer 2

这个标记的答案不能正常工作，请将日期更改为 "1899-12-30" 而不是 "1899-12-31"。

select dateadd(d,36464,'1899-12-30')

- Jacob Santiago

Answer 3

您可以将其转换为 SQL SMALLDATETIME：

CAST(36464 - 2 as SMALLDATETIME)

MS SQL Server将其日期从1900年1月1日开始计算，而Excel则从1899年12月30日开始计算，因此Excel比SQL Server少2天。

- Ricardo Souza

---

FYI：这个“少两天”的问题比你错误的评估要有趣得多。盖茨先生本人批准了日期的奇怪性。请参阅以下两个资源：微软支持、真相故事。同时，Stack Overflow的创始人确实在整个事件中起到了重要作用。 - bp_

我认为这个答案更加简洁，而且上面的评论提供了链接到Joel的博客文章，真是太棒了！ - kiltannen

Answer 4

tldr：

select cast(@Input - 2e as datetime)

解释：

Excel将日期时间存储为浮点数，表示自20世纪初以来经过的时间，而SQL Server可以轻松地在浮点数和日期时间之间进行转换。 Excel和SQL服务器将此数字转换为日期时间的差异为2天（截至1900年3月1日）。使用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

- Tim Lehner

Answer 5

这对我真的有用。

dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-30') 

(日期减少一天)

指的是负面评论帖子

- drinky

---

公式的第一部分中，“mi”是什么意思？ - Nicolaesse

mi = 分钟，作为 dateadd 公式中的 datepart。https://msdn.microsoft.com/zh-cn/library/ms186819.aspx - drinky

Answer 6

SSIS解决方案

DT_DATE数据类型是使用8字节浮点数实现的。天数由整数增量表示，从1899年12月30日开始，午夜为时间零点。小时值表示为数字的小数部分的绝对值。然而，浮点值无法表示所有实数值；因此，对于可以在DT_DATE中呈现的日期范围存在限制。阅读更多

从上述描述中可以看出，在将其转换为8字节浮点数DT_R8后，您可以在将这些值映射到DT_DATE列时隐式地进行转换。

使用派生列转换将此列转换为8字节浮点数：

(DT_R8)[dateColumn]

将其映射到一个DT_DATE列中

或者进行两次转换：

(DT_DATE)(DT_R8)[dateColumn]

您可以在此处查看我的完整答案：

• 在SSIS中解析[Integer].[Integer]样式日期是否有更好的方法？

- Hadi

Answer 7

我需要将翻译提升到更高的水平，因为我的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

（另外，为了让事情变得更加复杂，我的带小数点的数字值被保存为NVARCHAR）

我用的SQL转换如下：

SELECT DATEADD(SECOND, (
                        CONVERT(FLOAT, t.ColumnName) - 
                        FLOOR(CONVERT(FLOAT, t.ColumnName))
                       ) * 86400,
               DATEADD(DAY, CONVERT(FLOAT, t.ColumnName), '1899-12-30')
              )

- hurleystylee

Answer 8

发现这个主题非常有帮助，因此为其创建了一个快速的 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

- user692942

---

我使用类似的东西，但我发现将输入参数作为浮点数更加通用。 - Tim Lehner

Answer 9

在PostgreSQL中，您可以使用以下语法：

SELECT ((DATE('1899-12-30') + INTERVAL '1 day' * FLOOR(38242.7711805556)) + (INTERVAL '1 sec' * (38242.7711805556 - FLOOR(38242.7711805556)) * 3600 * 24)) as date

在这种情况下，38242.7711805556 表示 Excel 格式中的 2004-09-12 18:30:30。

- Alfonso Bribiesca

Answer 10

Google BigQuery 解决方案

标准 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
 )

- Selcuk Akbas

---

对于来到这里并寻找 BigQuery 更简短解决方案的人，可以尝试使用 SELECT DATETIME_ADD("1899-12-30",INTERVAL CAST(43909.91492 * 86400 AS INT64) SECOND)（另外，上面的答案似乎差了一秒；无论是 Excel 还是我的代码都将该序列日期定在 2018年9月3日16:23:29）。 - justbeez