flat file datetime issue

Asked Jul 12 '17 at 21:55

Active Jul 12 '17 at 22:51

Viewed 402 times

I have a flat file with this datetime format 5/24/2017 05:12:44.243 PM. I have been struggling to convert this format to something like this 2017-05-24 17:12:44.243. I tried using this derived column expression

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

where "EventDate" is the column name

but I am getting the result in this format "2017-05-22 05:21:00.000" also this is not converting the 12 hour to 24 hour. I am new to SSIS development and been struggling on this really long. Please help me on this, any help will be really appreciated.

edited Jul 12 '17 at 22:18

joanolo

13,657
8
39
67

asked Jul 12 '17 at 21:55

gdw

flat file datetime issue

0 Answers0