Data conversion for DateTime column from excel to SQL in SSIS

Question

Hi,

I am trying to load data from excel source where the date column has data like "Sun Mar 07 00:17:07 PST 2021" and I need to load it to SQL table with date time data type and my value supposed to be in following format mm/dd/yyyy hh:mm:ss. Please let me know the solution.

Excel Source Data:

Sun Mar 07 00:17:07 PST 2021

SQL Destination Table with datetime column:

mm/dd/yyyy hh:mm:ss

Thanks,
Sandeep

Answer 1

Hi @Tadishetty, Sandeep ,

We can use Derived Column and Data Conversion in ssis package.

Please refer to the following pictures:

Expression:
(DT_WSTR,10)(TOKEN(Col1," ",2) == "Jan" ? 1 : (TOKEN(Col1," ",2) == "Feb" ? 2 : (TOKEN(Col1," ",2) == "Mar" ? 3 : (TOKEN(Col1," ",2) == "Apr" ? 4 : (TOKEN(Col1," ",2) == "May" ? 5 : (TOKEN(Col1," ",2) == "Jun" ? 6 : (TOKEN(Col1," ",2) == "Jul" ? 7 : (TOKEN(Col1," ",2) == "Aug" ? 8 : (TOKEN(Col1," ",2) == "Sep" ? 9 : (TOKEN(Col1," ",2) == "Oct" ? 10 : (TOKEN(Col1," ",2) == "Nov" ? 11 : 12))))))))))) +"/"+(DT_WSTR,10)TOKEN(Col1," ",3) + "/" +(DT_WSTR,10)TOKEN(Col1," ",6) + " " + (DT_WSTR,10)TOKEN(Col1," ",4)

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.