SQL Server Home
SSIS (DT_DBDATE) Date Conversions
7/10/2007 8:59:19 AM
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.
SQL Server 2008 June CTP
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
1) Create a flat file with two rows
2) Create a package to load the file
3.1) Give the only column in Flat File column a data type of DT_DBDATE
3.2) Or, give the only column the in Flat File a data type of DT_STR
4) If 3.1
4.1) Configure the Error Output of the Flat File Source to Ignore Failure (or Redirect Rows)
4.2) Connect the Success output to a row file and a data data viewer
5) If 3.2
5.1) Use a Data Conversion Task or a Derived Column Task to convert the DT_STR column to DT_DBDATE
20070710 fails to convert
2007-07-10 converts succesfully
Both 20070710 and 2007-07-10 should convert succesfully.
Otherwise, Books online should describe DT_DBDATE as follows: "A date structure that consists of year, month, and day - where year, month and day are separated by hyphens".
to post a comment.
Please enter a comment.
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
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
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.
to post a workaround.
Please enter a workaround.
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))
© 2013 Microsoft