1

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.

Improve this question
7
  • Check SET DATEFORMAT and CAST and CONVERT (Transact-SQL). You may just not need to do any conversion, just let SQL Server know which is your original format, and let it import your flat files.
    – joanolo
    Jul 12, 2017 at 22:19
  • Is this field a datetime or a string?
    – McNets
    Jul 12, 2017 at 22:54
  • I already tried with out using any conversion but ended up getting in error in SSIS tranformation.
    – gdw
    Jul 12, 2017 at 22:54
  • this is a datetime field.
    – gdw
    Jul 12, 2017 at 22:55
  • Maybe this helps, sqlservercentral.com/Forums/1803581/…
    – McNets
    Jul 12, 2017 at 22:56

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.