Convert Julian 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’))

Input Flat File – Joining Date is in Julian Format (YYYYDDD)

Mapping to Convert Julian to Gregorian Date Using Expression Transformation

Create an output port with Date/Time datatype

Expression Editor – Expression to convert Julian to Gregorian

Session Log – All 5 input rows are processed and load to target.

Target Table – Dates are now converted to Gregorian date format. Since the input dates are not valid for the last two records, it’s been converted to hard coded default values (01-JAN-01).