SQL Server Home
SSIS Expression Date Functions Incomplete - Missing New Data Type Support
Darren Green SQLIS
1/3/2008 5:56:39 AM
User(s) can reproduce this bug
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.
SQL Server 2008 November CTP
Integration Services (DTS)
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
Add a Derived Column transform, using input column of type DT_DBTIMESTAMP2, and set an expression using YEAR, DATEADD or DATEPART.
TITLE: Microsoft Visual Studio
Error at Data Flow Task [Derived Column ]: The function "DATEADD" does not support the data type "DT_DBTIMESTAMP2" for parameter number 3. 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.
Error at Data Flow Task [Derived Column ]: Evaluating function "DATEADD" failed with error code 0xC0047089.
Error at Data Flow Task [Derived Column ]: Computing the expression "DATEADD("dd", 1, [DT23])" failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.
Error at Data Flow Task [Derived Column ]: The expression "DATEADD("dd", 1, [DT23])" on "output column "YR" (74)" is not valid.
Error at Data Flow Task [Derived Column ]: Failed to set property "Expression" on "output column "YR" (74)".
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
to post a comment.
Please enter a comment.
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?
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.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft