SSIS - OLEDate CAST vs Implicit conversion to DT_DBDATE vs DateTime.FromOADate()

About date serials (OLE Dates)

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

Date Serials are used in Microsoft Excel and many other platform.

You can Read more about Date Serials at:

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

Date Serials in SSIS

We can convert date serials to datetime  using  CAST method in the OLEDB Source SQL Command , or converting it using a script component DateTime.FromOADate() method, or we can connecting it directly to a DBTYPE_DATE column. but there is difference between these methods

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

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

3) DATETIME.FromOADate()

From MSDN - DateTime.FromOADate:

"The d parameter is a double-precision floating-point number that represents a date as the number of days before or after the base date, midnight, 30 December 1899"

So DateTime.FromOADate() will give the same result as SSIS Implicit conversion