SSIS (DT_DBDATE) Date Conversions - by M Vega1

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 286693 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/10/2007 8:59:19 AM
Access Restriction Public


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.