Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Julian Day, Modified Julian Day, and the Gregorian Calendar

Julian Day, Modified Julian Day, and the Gregorian Calendar

You can use dates in the Gregorian calendar system only. Dates in the Julian calendar are called Julian
dates
and are not supported in Informatica. This term should not be confused with Julian
Day
or with Modified Julian Day.
You can manipulate Modified Julian Day (MJD) formats using the J format string. The MJD for a given date is the number of days to that date since Jan 1 4713 B.C. 00:00:00 (midnight). By definition, MJD includes a time component expressed as a decimal, which represents some fraction of 24 hours. The J format string does not convert this time component.
For example, the following TO_DATE expression converts strings in the SHIP_DATE_MJD_STRING port to date values in the default date format:
TO_DATE (SHIP_DATE_MJD_STR, 'J')
SHIP_DATE_MJD_STR
RETURN_VALUE
2451544
Dec 31 1999 00:00:00.000000000
2415021
Jan 1 1900 00:00:00.000000000
SHIP_DATE_MJD_STR
RETURN_VALUE
2451544
Dec 31 1999 00:00:00.000000000
2415021
Jan 1 1900 00:00:00.000000000
Because the J format string does not include the time portion of a date, the return values have the time set to 00:00:00.000000000.
You can also use the J format string in TO_CHAR expressions. For example, use the J format string in a TO_CHAR expression to convert date values to MJD values expressed as strings. For example:
TO_CHAR(SHIP_DATE, 'J')
SHIP_DATE
RETURN_VALUE
Dec 31 1999 23:59:59
2451544
Jan 1 1900 01:02:03
2415021
The
PowerCenter Integration Service
ignores the time portion of the date in a TO_CHAR expression.