Home Dashboard Directory Help

SSIS (DT_DBDATE) Date Conversions by M Vega1


 as Fixed Help for as Fixed

Sign in
to vote
Type: Bug
ID: 286693
Opened: 7/10/2007 8:59:19 AM
Access Restriction: Public
User(s) can reproduce this bug


Books online gives the following description for DT_DBDATE: "A date structure that consists of year, month, and day".

The Data Conversion Task, the Derived Column Task and the Flat File Data Source fail to convert a date with the following format 20070710 to a DT_DBDATE data type. They can only convert a date with the following format 2007-07-10.

Sign in to post a comment.
Posted by Microsoft on 2/1/2008 at 11:13 AM
This information about the string formats to and from which datetime data types are converted is already contained in the BOL topic, "Integration Services Data Types" in the section "Converting String Data Types". While it might not be easy for everyone to find there, there does not seem to be a better place for this information. You will understand that we cannot reproduce all the information about each data type in every topic that talks about data types! Also, the topic on the Data Conversion Transformation links to the Data Types topic, to assist users in finding this information. Thank you for your feedback. -Doug

Posted by M Vega1 on 7/11/2007 at 10:04 AM
Using T-SQL, SELECT CAST(‘20070710’ as datetime) correctly converts the string to a DATETIME. I do not understand why the behavior of the Data Conversion task should be different
Posted by M Vega1 on 7/10/2007 at 9:01 AM
The workaround is (DT_DBDATE)(SUBSTRING(srcTransactionDate,1,4) + "-" + SUBSTRING(srcTransactionDate,5,2) + "-" + SUBSTRING(srcTransactionDate,7,2)). But it can take up to 5 minutes to type and test that expression. Writting the code to convert 5 dates using this workaround can take up to 5 minutes.
Sign in to post a workaround.
Posted by M Vega1 on 7/10/2007 at 9:04 AM
In a Derived Expression Task, use a expression like (DT_DBDATE)(SUBSTRING(srcTransactionDate,1,4) + "-" + SUBSTRING(srcTransactionDate,5,2) + "-" + SUBSTRING(srcTransactionDate,7,2))