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