CODEWITHSUNDEEP
datetimessistype-conversiondata-conversion
user2683470
user2683470

Reputation: 189

Convert date string to datetime in SSIS

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!

Upvotes: 2

Views: 13454

Answers (5)

Wichie Artu
Wichie Artu

Reputation: 125

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

Upvotes: 0

Shiv
Shiv

Reputation: 1404

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))

Upvotes: 1

jbhbs
jbhbs

Reputation: 1

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

Upvotes: -2

Justin
Justin

Reputation: 9724

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

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15017

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

Upvotes: 2

Related Questions