Date conversion handling YYYY-MM-DD HH:MM:SS.SSS

Question

My source is a file and loading into SQL Server table. I'm working on a scenario where i have to convert a string '2019-04-02T21:24:00.065' to informatica datetime format.

I tried below expression but some times its failing due to we are not receiving milliseconds from our source file in few occasions.

IIF(NOT ISNULL(DATEFIELD),TO_DATE(SUBSTR (DATEFIELD, 0, 10) || ' ' || SUBSTR(DATEFIELD, 12, 12), 'YYYY-MM-DD HH24.MI.SS.US'),NULL)

I'm looking for a permanent fix to handle all types of datetime formats regardless of what we receive in the file.

Answer 1

Well... I'm sorry to say, but there is no magic component that will recognize all possible date and time formats (including e.g. verbal in swahili). You will need to detect the format for yourself. You can use a DECODE function, like e.g.:

DECODE(True,
IS_DATE(your_input_port, 'DD/MM/YYYY'), TO_DATE(your_input_port, 'DD/MM/YYYY'),
...)

Answer 2

If you are completely sure that only seconds/milliseconds are the missing part, you can check for length, if less than 12, use RPAD to the second part of your SUBSTR with missing format, or you can use decode as suggested by @maciejg and write code for all possible date formats.

Answer 3

Thanks for your inputs guys. Since we are not sure which date format we are receiving , we decided to go with a simple fix for this. I have changed the target field to varchar and simply replacing the T with ' ' value since this a staging mapping.

Again thanks for your time and inputs.