CAST vs ssis data flow implicit conversion difference

Question

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

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

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.

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

Can anyone explain this?

Answer 1

About date serials

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

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

You can Read more about Date Serials at:

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

CAST method

From Microsoft Docs - CAST and CONVERT (Transact-SQL):

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

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

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 Implicit conversion

Also according to this Microsoft docs article

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

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