The SSIS expression date related functions have not been updated to include support for new date data types. The documentation states, "A date literal must be explicitly cast to one of the date data types. For more information, see Integration Services Data Types." DT_DBTIMESTAMP2 seems a nice date type, but functions such as YEAR, DATEPART and DATEADD do not support it. Example error when a DT_DBTIMESTAMP2 column is used in the YEAR function through a Derived Column Transformation -
The function "YEAR" does not support the data type "DT_DBTIMESTAMP2" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
This follows on from the incomplete support in 2005, with the functions being limited to dates >= 1753. Despite the date type DT_DBTIMESTAMP supporting a wider date range, the functions did not.
In previous bugs logged on Connect it was justified that the limited date range support of the functions was consistent with T-SQL functions, and therefore by design. This never did add-up when the data type was inconsistent. Now we have a consistent data types of datetime2 and DT_DBTIMESTAMP2, can we get some consistency back in the functions.
The T-SQL YEAR function supports the full range and scale of datetime and datetime2, the SSIS expression syntax should do so too, and for all date data types as documented.