JULIAN_DAY_OF_DATE() - Convert date

Function group: numeric function

JULIAN_DAY_OF_DATE() returns the Julian day number which corresponds to a given date time value (see also the inverse function “DATE_OF_JULIAN_DAY()” on "DATE_OF_JULIAN_DAY() - Convert Julian day number").

The Julian day number for the 24th November 4714 B.C. (in accordance with the Gregorian calendar) is “0”.

The Julian day number for a later date is the number of days which have passed between the 24th November 4714 B.C. and the later date. For example, the DATE '0001-01-01' corresponds to the Julian day number “1721426”, the DATE '9999-12-31' corresponds to the Julian day number “5373484”.

DATE_OF_JULIAN_DAY() and JULIAN_DAY_OF_DATE() are inverse functions. When, for example, a constraint exists in the form JULIAN_DAY_OF_DATE( column) < :user_variable, the SQL Optimizer can then convert this constraint internally to the constraint column < DATE_OF_JULIAN_DAY( :user_variable) in order to permit the use of indexes on column. Consequently :user_variable may only contain values which are permitted as an argument of DATE_OF_JULIAN_DAY(). This also applies for any constant expressions in place of :user_variable.

---

JULIAN_DAY_OF_DATE ( expression )

---

expression

Time value expression whose evaluation gives a value of the DATE data type; value is between 0001-01-01 and 9999-12-31.
expression may not be a multiple value with dimension > 1.

Result

When expression returns the NULL value, the result is the NULL value.

Otherwise:

the result is the Julian day number which represents the date which results from expression.

Data type: INTEGER

Examples

JULIAN_DAY_OF_DATE(DATE'2000-01-01')

2451545

To create a view which outputs the orders for the last two weeks:

CREATE VIEW orders AS SELECT * FROM job

WHERE todate >= DATE_OF_JULIAN_DAY(JULIAN_DAY_OF_DATE(CURRENT_DATE)-14)