How to Convert Julian to Gregorian date in Informatica

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

How to Convert Julian to Gregorian date in Informatica
Input Flat File – Joining Date is in Julian Format (YYYYDDD)
How to Convert Julian to Gregorian date in Informatica
Mapping to Convert Julian to Gregorian Date Using Expression Transformation
How to Convert Julian to Gregorian date in Informatica
Create an output port with Date/Time datatype
How to Convert Julian to Gregorian date in Informatica
Expression Editor – Expression to convert Julian to Gregorian
How to Convert Julian to Gregorian date in Informatica
Session Log – All 5 input rows are processed and load to target.
How to Convert Julian to Gregorian date in Informatica
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).

You May Also Like

About the Author: kvt

Leave a Reply

Your email address will not be published. Required fields are marked *