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.
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'),
...)