CAST与SSIS数据流隐式转换的区别

Question

我有一个SSIS包，用于将一些数据从Oracle传输到SQL Server。

在Oracle中，日期以浮点数形式存储，例如42824 == '2017-04-01' - 使用该数据库的应用程序是用Delphi编写的。

虽然在Management Studio中执行select CAST(42824 as datetime)的结果为'2017-04-01 00:00:00.000'，但相同的值（42824）由包插入到SQL Server表中的datetime列中时，显示为2017-03-30 00:00:00.000。

注：此数字的源数据类型为DT_R8，在数据转换组件中将类型更改为DT_UI4不会改变任何内容

请问有人能解释这个问题吗？

- avb

Answer 1

关于日期序列

Oracle 中存储的值（42824）被称为日期序列，它也在 Microsoft Excel 中使用。

日期序列表示日期值与初始值 1899-12-30 之间的天数。

您可以在以下链接中了解更多关于日期序列的内容：

• 为什么 Access / SQL Server 中的零日期是 1899-12-30 而不是 12/31？
• 将 Excel 日期序列号转换为常规日期

CAST 方法

来自 Microsoft Docs - CAST and CONVERT (Transact-SQL)：

仅在从字符数据转换为 datetime 或 smalldatetime 时受支持。当仅表示日期或时间组件的字符数据被转换为 datetime 或 smalldatetime 数据类型时，未指定的时间组件设置为 00:00:00.000，未指定的日期组件设置为 1900-01-01

因此，CAST 函数在转换日期时将值 1900-01-01 视为初始值。因此，在使用它来转换日期序列时，我们需要减去 2 天。

有两种方法可以使用 SQL Server 将其转换为日期：

select DATEADD(d,42824,'1899-12-30')

select CAST(36464 - 2 as SmallDateTime)

SSIS隐式转换

根据这篇Microsoft文档文章，DBTYPE_DATE（这是一种自动化DATE类型。它在内部表示为double。整数部分是自1899年12月30日以来的天数，小数部分是一天的分数。这种类型的精度为1秒，因此有效比例为0）。

因此，在SSIS中进行隐式转换时，将值1899-12-30视为转换日期的初始值。因此，在使用它来转换日期序列时，无需减去2天。

- Hadi

---

感谢您的回复。我知道其中的区别，事实上我在我的Oracle查询中确实写了select <data_as_integer> - 2。问题是最终我得到了-4！不知何故，它看起来像是Integration services“知道”这应该被视为日期并再次减去了2。正如我所写的，如果我将相同（整数）值显式地从管理工作室和SSIS包插入到datetime中，我会得到两个不同的日期。 - avb

在SSIS中，你如何将整数转换为日期？ - Hadi

明确地说，我并不是。我只是在数据流任务中将来自ODBC源的整数列映射到OLEDB目标中的日期时间列。源SSIS数据类型为DT_R8。 - avb

"DBTYPE_DATE (This is an automation DATE type. It is internally represented as a double.. The whole part is the number of days since December 30, 1899 and the fractional part is the fraction of a day. This type has an accuracy of 1 second, so has an effective scale of 0.)" - Hadi

就是这样 :) 我必须说，我觉得微软在不同的工具中甚至在一个工具中使用不同的方法有点奇怪，即使集成服务是 SQL Server 的一部分。无论如何，非常感谢。 - avb