String was not recognized as a valid DateTime

Question

Hi,

I am trying to store the sysdate into oracle table and trying this with copy data activity.

In mapping, I always get String datatype and sink has a DateTime datatype.

I added DateTime format : dd.MM.yyyy hh:mm:ss but not working.

Details

ErrorCode=TypeConversionFailure,Exception occurred when converting value '18.05.2023 18:24:14' for column name 'DT_1' from type 'String' (precision:19, scale:0) to type 'DateTime' (precision:19, scale:0). Additional info: String was not recognized as a valid DateTime.

Any help is appreciated!

Answer 1

Hi @John , the error message indicates that the string '18.05.2023 18:24:14' is not recognized as a valid DateTime.

To resolve this issue, you can try using the toTimestamp function in the mapping data flow to convert the string to a timestamp with the correct format. The default pattern for toTimestamp is yyyy-[M]M-[d]d hh:mm:ss[.f...]. Since your date format is different, you can provide the appropriate format as an argument to the toTimestamp function.

Here's an example:

toTimestamp('18.05.2023 18:24:14', 'dd.MM.yyyy hh:mm:ss')

This should convert the string '18.05.2023 18:24:14' to a valid DateTime format that can be stored in the Oracle table. Make sure to adjust the format string according to your specific date format.

Please let me know if you have any questions and I can help you further.

If this answer helps you please mark "Accept Answer" so other users can reference it.

Thank you,

James