question

KLokesh-0564 avatar image
0 Votes"
KLokesh-0564 asked Monalv-msft commented

Convert in IST time in normal date time format in ssis

Hi friends
I was processing the data from Excel source to SQL server destination
I'm facing some problem in data in source it was come like "2 mar 2015 2:55:55 PM IST" but i need date time like this 2015-03-02 14:55:55 any one can help me please

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered

Hi @KLokesh-0564 ,

We can use Derived Column Transformation in SSIS Data Flow Task to get the result.

Please refer to the following expression and pictures:

 (DT_DBTIMESTAMP)((DT_WSTR,10)TOKEN(Date," ",3) + "-" + (DT_WSTR,10)(TOKEN(Date," ",2) == "jan" ? 1 : (TOKEN(Date," ",2) == "feb" ? 2 : (TOKEN(Date," ",2) == "mar" ? 3 : (TOKEN(Date," ",2) == "apr" ? 4 : (TOKEN(Date," ",2) == "may" ? 5 : (TOKEN(Date," ",2) == "jun" ? 6 : (TOKEN(Date," ",2) == "jul" ? 7 : (TOKEN(Date," ",2) == "aug" ? 8 : (TOKEN(Date," ",2) == "sep" ? 9 : (TOKEN(Date," ",2) == "oct" ? 10 : (TOKEN(Date," ",2) == "nov" ? 11 : 12))))))))))) + "-" + (DT_WSTR,10)TOKEN(Date," ",1) + " " + (DT_WSTR,10)TOKEN(Date," ",4) + " " + (DT_WSTR,10)TOKEN(Date," ",5))

85019-df-output.png
85020-derivedcolumntransformation.png

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KLokesh-0564 avatar image
0 Votes"
KLokesh-0564 answered Monalv-msft commented

Hi Mona thanks for your answer thanks you so much 🥰🥰🥰

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KLokesh-0564 ,

It's my pleasure.

Welcome to contact us if you have any new issue.

Best regards,
Mona

0 Votes 0 ·