CAST vs ssis 数据流隐式转换差异

2021-12-30 00:00:00 sql etl oracle sql-server ssis

我有一个 SSIS 包，可以将一些数据从 Oracle 传输到 SQL Server.

I have a SSIS package which transfers some data from Oracle to SQL Server.

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

In Oracle dates are stored as float, e.g. 42824 == '2017-04-01' - application which uses the database is written in Delphi.

虽然 选择 CAST(42824 作为日期时间)
在 Management Studio 结果 '2017-04-01 00:00:00.000' 中，包插入到 SQL Server 表中日期时间列的相同值 (42824) 显示 2017-03-3000:00:00.000.

While select CAST(42824 as datetime)
in Management Studio results in '2017-04-01 00:00:00.000', the same value (42824) inserted by package into datetime column in SQL Server table shows 2017-03-30 00:00:00.000.

注意:这个数字的源数据类型是DT_R8，在数据转换组件中将类型更改为DT_UI4没有任何改变

Note: Source data type for this number is DT_R8, changing the type to DT_UI4 in Data Conversion component changes nothing

谁能解释一下?

推荐答案

关于日期序列

存储在 Oracle (42824) 中的值称为日期序列，它也用于 Microsoft Excel.

About date serials

The value stored in Oracle (42824) is known as date serial , it is also used in Microsoft Excel.

Date Serial 表示日期值与初始值 1899-12-30

Date Serial represents the number of Days between the date value and the initial value that is 1899-12-30

您可以在以下位置阅读有关日期序列的更多信息:

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

来自 Microsoft Docs- CAST 和 CONVERT (Transact-SQL):

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

Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01

所以 CAST 函数在转换日期时将值 1900-01-01 视为初始值.所以我们用它来转换Date Serials时需要减去2天

So CAST function consider the value 1900-01-01 as an initial value when casting dates. So we need to subtract 2 days when using it to convert Date Serials

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

There are 2 ways to convert it to date using SQL Server:

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

select CAST(36464 - 2 as SmallDateTime)

SSIS 隐式转换

也根据这篇微软文档文章

DBTYPE_DATE(这是一个自动化的 DATE 类型.它在内部表示为一个双精度数.整个部分是自 1899 年 12 月 30 日以来的天数，小数部分是一天的分数.这种类型具有准确性1 秒，因此有效尺度为 0.)

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.)

因此在 SSIS 中的隐式转换在转换日期时将值 1899-12-30 视为初始值.所以用它来转换Date Serials时不需要减去2天

So implicit conversion in SSIS consider the value 1899-12-30 as an initial value when casting dates. So there is no need to subtract 2 days when using it to convert Date Serials

相关文章