Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

Julian Day, Modified Julian Day, and the Gregorian calendar

Julian Day, Modified Julian Day, and the Gregorian calendar

Data Integration
supports dates in the Gregorian calendar system only. Dates expressed in a different calendar system are not supported.
Dates in the Julian calendar are called Julian
dates
and are not supported in
Data Integration
. This term should not be confused with Julian
Day
or with Modified Julian Day.
The transformation language provides the ability to 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 BC 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 field 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
2415021
Jan 1 1900 00:00:00
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.
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
Data Integration
ignores the time portion of the date in a TO_CHAR expression.

0 COMMENTS

We’d like to hear from you!