将 Excel 日期序列号转换为常规日期



我的csv文件中有一个名为DateOfBirth的列,其中包含Excel日期序列号日期

例:

36464
37104
35412

当我在 excel 中格式化单元格时,这些单元格被转换为

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

我需要在 SSIS 或 SQL 中执行此转换。 如何实现这一点?

在 SQL 中:

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

在 SSIS 中,请参阅此处

http://msdn.microsoft.com/en-us/library/ms141719.aspx

标记的答案不起作用,请将日期更改为"1899-12-30"而不是"1899-12-31"。

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

你可以把它投射到一个SQLSMALLDATETIME

CAST(36464 - 2 as SMALLDATETIME)

MS SQL Server 从1900 年1 月 1 日开始计算其日期,从1899 年 12 月 30日开始计算 Excel = 少 2 天。

TLDR:

select cast(@Input - 2e as datetime)

解释:

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

这实际上对我有用

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

(减去日期中多 1 天)

参考负面评论帖子

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 中的整数] 样式日期?

发现这个主题非常有用,所以为它创建了一个快速的 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

我必须将其提升到一个新的水平,因为我的 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')
)

在 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

除了@Nick.McDermaid的答案之外,我还想发布这个解决方案,它不仅可以转换日期,还可以转换小时,分钟和秒:

SELECT DATEADD(s, (42948.123 - FLOOR(42948.123))*3600*24, dateadd(d, FLOOR(42948.123),'1899-12-30'))

例如

  • 42948.1232017-08-01 02:57:07.000
  • 42818.71666666672017-03-24 17:12:00.000

如果您只需要在视图中显示日期,则可以执行此操作:

CAST如果您有大量数据,CONVERT会比快,还记得从 excel 日期中减去 (2):

CAST(CAST(CAST([Column_With_Date]-2 AS INT)AS smalldatetime) AS DATE)

如果需要更新列以显示日期,可以通过联接(必要时自行联接)进行更新,或者直接尝试以下操作:

您可能不需要将 excel 日期转换为 INT,但由于我正在使用的表是 varchar,因此我必须先进行该操作。我也不想要"time"元素,所以我需要删除该元素,最终强制转换为"date"。

UPDATE [Table_with_Date]
SET [Column_With_Excel_Date] = CAST(CAST(CAST([Column_With_Excel_Date]-2 AS INT)AS smalldatetime) AS DATE)

如果您不确定要对此测试做什么并重新测试!如果需要,请复制表格。您可以随时创建视图!

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
)

对于那些正在寻找如何在excel中执行此操作(除了格式化为日期字段之外)的人,您可以使用文本函数 https://exceljet.net/excel-functions/excel-text-function

A1 = 132134
=Text(A1,"MM-DD-YYYY") will result in a date

这对我有用,因为有时该字段是一个数字来获取时间部分。

命令:

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

最新更新