SSIS Expression Date Functions Incomplete - Missing New Data Type Support - by Darren Green SQLIS

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 320897 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/3/2008 5:56:39 AM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Matt Christenson on 7/13/2013 at 4:21 PM
I have also experienced this issue and I feel that Microsoft's response to this is completely inacceptable. Using SQL 2008R2 casting the type to a 'data time that is expressionable' is IMPOSSIBLE if values that we are dealing with REQUIRE the datetime2 type. So the function is useless. Because I'm pulling data from access, another Microsoft product, (where dates like 01/01/0123 are supported in the standard date range, and unfortunately exist in my source data) trying to convert small date values to values over the year 1753 is proving to be a nightmare. If you're not going to provide the necessary tools to work with the datetime2 type, why bother adding it?
Posted by Microsoft on 3/27/2008 at 10:13 AM
Thanks for providing this feedback. Unfortunately the fix for this has proved to be much more complex than anticipated and we do not feel it would be a safe change to bring into this release at this time. We are moving this issue to the next major release where we will do our best to find a good, long term solution to this problem. As a work-around you can use type conversion transform to change the date type to one that is expressionable. Sorry we don't have better news on this one as we do recognize it is a significant problem.

Thanks again,
-Matt Carroll