How to convert MM/DD/YYYY HH:MM:SS.SSS text to a date in SSIS 2012

2
votes

I am coming from an Oracle background and cannot figure out how to convert a date string from a flat file (in MM/DD/YYYY HH:MM:SS.SSS format) to a date type in SSIS.

An example of the date string is: 10/9/2013 11:29:24.933

What date format should I use and how should I do it?

Assume that both day and month won't have leading zeros, the hour is in 24 hour time, and I need to maintain the seconds' precision.

2
datetimessis

2 Answers

5
votes

Derived Column code:

(DT_DBTIMESTAMP)(SUBSTRING(datestr,FINDSTRING(datestr,"/",2) + 1,4) + "-" + 
 SUBSTRING(datestr,1,FINDSTRING(datestr,"/",1) - 1) + "-" +
 SUBSTRING(datestr,FINDSTRING(datestr,"/",1) + 1,FINDSTRING(datestr,"/",2) - 
                                                 FINDSTRING(datestr,"/",1) - 1) 
 + " " + SUBSTRING(datestr,FINDSTRING(datestr," ",1) + 1,8))

Data:

datestr
5/9/2013 11:29:24.933
11/10/2013 11:29:24.933
10/9/2013 11:29:24.933

Result:

datestr                 Derived Column 6
10/9/2013 11:29:24.933  2013-10-09 11:29:24.000
5/9/2013 11:29:24.933   2013-05-09 11:29:24.000
11/10/2013 11:29:24.933 2013-11-10 11:29:24.000
0
votes

What I am found is,

If your source date is stored as text in excel as MM/DD/YYYY format, then pls make sure your system date setting is same else change it. Go to Regional and language change short date and vice versa.

Check this scenario. It worked for me.