convert YYYYMMDD to datetime in ssis

Question

convert YYYYMMDD to datetime in ssis

Naresh y 146

Hi I have date filed in my source column of string data type
datecolumn
20210521
20211005
i want to convert this value to datetime which is in my target table ,format like
2021-05-21 00:00:00.000
2021-10-05 00:00:00.000

i tried converting in the sql sselect script and try to map to the destination but its not working

please help me to define the derived column exp in ssis ,how to write the exp to write to change this date column values in ssis? please suggest

Yitzhak Khabinsky 27,116 Reputation points

2022-02-14T14:57:56.83+00:00
@Naresh y ,

In theory you don't need any conversion.
Data type DATE will be perfectly loaded into a DATETIME data type and will add zeros to its time portion.
Please add the following to your original question:

A screen shot of the package DFT.

Exact error message.
Kumar, Anil 0 Reputation points

2023-02-27T11:42:42.0233333+00:00

you can use TryConvert function

5 answers

Your answer

Yitzhak Khabinsky 27,116 Reputation points

2022-02-14T14:57:56.83+00:00

@Naresh y ,

In theory you don't need any conversion.
Data type DATE will be perfectly loaded into a DATETIME data type and will add zeros to its time portion.
Please add the following to your original question:

A screen shot of the package DFT.

Exact error message.
Kumar, Anil 0 Reputation points

2023-02-27T11:42:42.0233333+00:00

you can use TryConvert function

Answer 1

Kumar, Anil 0

Use tryConvert function

0 comments

Answer 2

Olaf Helper 47,616

I tried using the data conversion but its failing

And which error message do you get (we can't guess that)?

0 comments

Answer 3

Hi @Naresh y ，

Have you check your previous question in Microsoft Q&A?

how-to-convert-the-fixed-width-date-column-into-th.html

You may add the Derived Column with below code. (DT_DBTIMESTAMP)(SUBSTRING(datecolumn,1,4) + "-" +SUBSTRING(datecolumn,5,2) + "-" + SUBSTRING(datecolumn,7,2))

2.You may add the Data Conversion. Choose DT_DBTIMESTAMP as datatype.

And then Go to Input and Output Properties---Expand Data Conversion Output and click on the date column---Set FastParse to True in the Custom Properties.

If this do not help, you may show the error message for us to do more analysis.

If your issue has been resolved please mark the reply as answer so other user with similar problem could see this easier. :)

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

ZoeHui-MSFT 41,551 Reputation points

2022-02-16T09:19:15.01+00:00

Hi @Naresh y ，
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 4

Naresh y 146

I tried using the data conversion but its failing
can you help with the expression

0 comments

Answer 5

Olaf Helper 47,616

In T-SQL

select CONVERT(datetime, '20210521', 112);

See CAST and CONVERT (Transact-SQL)

For SSIS see Convert Data Type with Data Conversion Transformation

0 comments

Share via

convert YYYYMMDD to datetime in ssis

5 answers

Your answer