CAST vs ssis数据流隐式转换差异 [英] CAST vs ssis data flow implicit conversion difference

问题描述

我有一个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.

select CAST(42824 as datetime)
在Management Studio中，结果为'2017-04-01 00:00:00.000'，则由包插入SQL Server表的datetime列中的相同值(42824)显示为2017-03-30 00: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.

日期序列表示日期值和初始值之间的天数，该初始值是1899-12-30

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

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

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

• Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
• convert Excel Date Serial Number to Regular Date

来自 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视为初始值.因此，使用它转换日期序列时需要减去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隐式转换

也请按照此 Microsoft文档文章

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视为初始值.因此，使用它转换日期序列时无需减去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

这篇关于CAST vs ssis数据流隐式转换差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！