Julian Dates (J Format)
The Julian date format enables you to represent dates before
December 31, 1840. To use this format, specify the format argument
of TO_DATE as 'J' or 'j'. Using this format, you can convert a seven-digit internal
numeric value (a Julian day count) to a formatted date. For example,
this statement returns 1585–01–31 in
Logical or ODBC format and 01/31/1585 in Display
format.
SELECT TO_DATE(2300000,'J')
The Julian day count value of 1721424 returns January 1st of
Year 1 (1–01–01) in the Julian calendar.
Julian day counts less than this values return BCE dates, which are
displayed with the year preceded by a minus sign.
By default, the %DateOpens in a new tab data type does
not represent dates prior to December 31,
1840. However, you can redefine the MINVAL parameter for this
data type to permit representation of earlier dates as negative integers,
with the limit of January 1, Year 1. This representation of dates
as negative integers is not compatible with the Julian date format
described here. For more details, see Data
Types.
A Julian day count is always represented internally as a seven-digit
number, with leading zeros when necessary. TO_DATE allows you to input a Julian day count without the leading zeros.
The highest permitted Julian date is 5373484, which returns 12/31/9999. The lowest permitted Julian date is 0000001,
which returns 01/01/-4712 (01/01/4713 BCE). Any
value outside this range generates an SQLCODE -400 error.
Julian day counts prior to 1721424 (1/1/1) are compatible with
other software implementations, such as Oracle. They are not identical to BCE dates in ordinary usage. In ordinary
usage, there is no Year 0 and dates go from 12/31/-1 to 1/1/1. In
Oracle usage, the Julian dates 1721058 through 1721423 are simply
invalid, and return an error. In InterSystems IRIS, these Julian dates
return the non-existent Year 0 as a place holder. Thus calculations
involving BCE dates must be adjusted by one year to correspond to
common usage. This should not affect the conversion of dates and Julian
day counts using TO_CHAR and TO_DATE, but it might affect some calculations made using Julian day counts.
Also, be aware that these date counts do not take into account changes
in date caused by the Gregorian calendar reform.
TO_DATE permits you to return a date expression
corresponding to a Julian day count. TO_CHAR permits
you to return a Julian day count corresponding to a date expression,
as shown in this example:
SELECT
TO_CHAR('1776-07-04','J') AS JulianCount, -- 2369916
TO_DATE(2369916,'J') AS JulianDate -- 1776-07-04