Convert date string to datetime in SSIS

2
votes

I am having trouble converting date string to datetime in SSIS. The problem is that the source data store dates in this format: m(m)/d(d)/yyyy, i.e. 3/8/2014, 10/5/2013, 12/22/2014. I have already tried to use data conversion and it won't work. Also I have checked data quality and they are all good - no weird strings. Does anyone know how to fix the package please?

Thanks a lot!

5
datetimessistype-conversiondata-conversion
How did you try to convert the dates? What type, format did you use? Is the source type a string or are you converting a datetime to a string while loading it in SSIS? – Panagiotis Kanavos
"I have already tried to use data conversion and it won't work." What did you try, what was the input data, and what did the failure look like? – Jon Skeet
@JonSkeet, thank you for the feedback. The source is Excel sheet. Before converting the data, I have a derived column to get rid of irregular data and left this field either empty or the Unicode string of the format above. In the Data Conversion transformation, I put data type as [DT_DBTIMESTAMP]. While running the package, I got error: "The value could not be converted because of a potential loss of data" – user2683470
@PanagiotisKanavos thank you for your feedback. please see my comment above – user2683470
@user2683470: Well it's still not clear what you mean. Showing your code would make it easier to understand... – Jon Skeet

5 Answers

2
votes

I would use a Script Transformation for this. You can then leverage the .NET Framework which has far superior functionality e.g. DateTime.TryParse.

1
votes

First you need to convert Date to unicode string and now you can convert with derived column element.

Derived Column Code:

(DT_DATE)(SUBSTRING(col,FINDSTRING(col,"/",2) + 1,4) + "-" +
          SUBSTRING(col,1,FINDSTRING(col,"/",1) - 1) + "-" + 
          SUBSTRING(col,FINDSTRING(col,"/",1) + 1,FINDSTRING(col,"/",2) - FINDSTRING(col,"/",1) - 1))

Result:

col         NewDateColumn
3/8/2014    2014-03-08 00:00:00.0000000
10/5/2013   2013-10-05 00:00:00.0000000
12/22/2014  2014-12-22 00:00:00.0000000
1
votes

This is what I used. It formats the col to yyyy-MM-dd format then converts to a DT_DBDATE so if you just want the date string you can remove the wrapping cast.

(DT_DBDATE)(SUBSTRING(MYCOL,FINDSTRING(MYCOL,"/",2) + 1,4) + "-" + RIGHT("0" + SUBSTRING(MYCOL,1,FINDSTRING(MYCOL,"/",1) - 1),2) + "-" + RIGHT("0" + SUBSTRING(MYCOL,FINDSTRING(MYCOL,"/",1) + 1,FINDSTRING(MYCOL,"/",2) - FINDSTRING(MYCOL,"/",1) - 1),2))
0
votes

I edited the source so it exported the value as yyyy-MM-dd HH:mm:ss

So, when it went through SSIS, it was able to convert it natively through the Data Conversion Transformation tool

-2
votes

Try converting to a date then a datetime using (DT_TIMESTAMP)(DT_DBDATE).