How to Convert Julian date to Gregorian date in Informatica

Dates in the Julian calendar are called Julian dates which are not supported in Informatica. We need to convert the date to Gregorian calendar for further informatica processing.

We can use TO_DATE function to convert the Julian date to Gregorian in an expression and then load to target. The TO_DATE function converts a string with the format you specify to a date-time value. TO_DATE is generally used to convert strings from flat files to date-time values.

TO_DATE( DATE_JULIAN, ‘YYYYDDD’)

Let’s see an example, I have a flat file with employee joining date in Julian. I want to load this date as Gregorian to my target table.

Julian Date : 2015001 (YYYYDDD)
Converted Date : 01-JAN-15

We can use the below expression in an output port using Expression transformation. This will check whether the input julian date is in ‘YYYYDDD’ format, if YES TO_DATE will convert the date to Gregorian and if NO To_DATE will use a default date of 01-01-0001 (01-JAN-01).

IIF(IS_DATE(Date_Join_Julian,’YYYYDDD’)=TRUE,
TO_DATE(Date_Join_Julian,’YYYYDDD’),TO_DATE(‘01010001′,’MMDDYYYY’))