Date conversion

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

Phil,

That cannot be explicitly casted as a DT_DBTIMESTAMP because of the "-" between days and hours and the "."'s in the time. The following expression in a derived column expression should work:

(DT_DBTIMESTAMP)(SUBSTRING(<column_name>, 1,10) + " " + SUBSTRING(<column_name>, 12,2) + ":" + SUBSTRING(<column_name>, 15,2) + ":" + SUBSTRING(<column_name>, 18,2))

OK, I've done this from memory so it might not work exactly correctly but hopefully you get the idea and you can modify it appropriately.

-Jamie

|||Thanks Jamie.|||

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

|||

ckeaton wrote:

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1|||Thank you!