date conversion in SSIS package

Dear Experts,
I have a file that was originally coming through with date formats as (mm/dd/yyyy) vs. the new format (yyyymmddhhmmss???). I have attached the new file. Can you please suggest how I can change the date format to the new format.
Please find attached the picture of the flow that I was using for the old file with date formats as (mm/dd/yyyy) . Please let me know how I can change the date format, should I add a derived column and then change the field types?
Thanks!! main_flow data_conversionSERIAL-DAILY.txt
sqlcuriousAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sqlcuriousConnect With a Mentor Author Commented:
shamu....this should work...

(DT_DBTIMESTAMP)(SUBSTRING([SHIP DATE],1,4) + "-" + SUBSTRING([SHIP DATE],5,2) + "-" + SUBSTRING([SHIP DATE],7,2) + " " + SUBSTRING([SHIP DATE],9,2) + ":" + SUBSTRING([SHIP DATE],11,2) + ":" + SUBSTRING([SHIP DATE],13,2) + "." + SUBSTRING([SHIP DATE],5,3))
0
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
hi

make it simple add a derived column and CAST to the needed format.
0
 
sqlcuriousConnect With a Mentor Author Commented:
Hi tried this, but it is giving an error
(DT_DBTIMESTAMP)(SUBSTRING(IntervalStartDateTime,5,2) + "-" + SUBSTRING(IntervalStartDateTime,7,2) + "-" + SUBSTRING(IntervalStartDateTime,1,4) + " " + SUBSTRING(IntervalStartDateTime,9,2) + ":" + SUBSTRING(IntervalStartDateTime,11,2) + ":00")


0
 
sqlcuriousAuthor Commented:
And there is one record in your text file with BPRA# 5045105 which has null return date and null shipdate….so that record will fail in the derivedcolumn transformation…so try to redirect that record to the error text file….hope this helps….
0
 
sqlcuriousAuthor Commented:
problem solved
0
All Courses

From novice to tech pro — start learning today.