Julian date in SQL

The two meanings, so you convert the right one:

• Ordinal date — a year plus the day number 1-365/366: 2026202 (YYYYDDD) or 26202 (YYDDD). July 21, 2026 is day 202.
• Astronomical Julian Day — a continuous count of days since noon on 1 January 4713 BC. The integer Julian Day Number for July 21, 2026 is 2461243.

Day-of-year function by database

SQL Server (T-SQL)

Day of year and ordinal codes

DATEPART(dayofyear, ...) (short form dy) gives the 1-366 day number; build the codes with string concatenation:

DECLARE @d date = '2026-07-21';

-- Day of year (1-366)
SELECT DATEPART(dayofyear, @d) AS doy;   -- 202
SELECT DATENAME(dayofyear, @d) AS doy;   -- '202' (as text)

-- YYYYDDD, the 7-digit ordinal "Julian" code
SELECT CAST(YEAR(@d) AS CHAR(4))
     + RIGHT('000' + CAST(DATEPART(dy, @d) AS VARCHAR(3)), 3) AS julian7;  -- 2026202

-- Same result with FORMAT (SQL Server 2012+)
SELECT FORMAT(@d, 'yyyy')
     + FORMAT(DATEPART(dy, @d), '000') AS julian7;   -- 2026202

-- YYDDD, the 5-digit code
SELECT RIGHT(CAST(YEAR(@d) AS CHAR(4)), 2)
     + RIGHT('000' + CAST(DATEPART(dy, @d) AS VARCHAR(3)), 3) AS julian5;  -- 26202

Astronomical Julian Day

SQL Server has no Julian-Day function, so anchor to a known JDN. January 1, 2000 at noon is JDN 2451545, and DATEDIFF(day, ...) returns whole days:

DECLARE @d datetime2 = '2026-07-21T00:00:00';

-- Integer Julian Day Number (JDN)
SELECT DATEDIFF(day, '2000-01-01', @d) + 2451545 AS jdn;  -- 2461243

-- Full JD with fraction (JD rolls over at noon, so 00:00 = x.5)
SELECT DATEDIFF(day, '2000-01-01', @d) + 2451545
     + (DATEPART(hour, @d) - 12) / 24.0
     + DATEPART(minute, @d) / 1440.0
     + DATEPART(second, @d) / 86400.0 AS jd;   -- 2461242.5

Oracle

Day of year and ordinal codes

Oracle formats everything through TO_CHAR. Handily, 'YYYYDDD' produces the 7-digit ordinal code in one call, and TO_DATE(..., 'YYYYDDD') parses it back:

-- Day of year and ordinal codes
SELECT TO_CHAR(DATE '2026-07-21', 'DDD')     AS doy      FROM dual;  -- 202
SELECT TO_CHAR(DATE '2026-07-21', 'YYYYDDD') AS julian7  FROM dual;  -- 2026202
SELECT TO_CHAR(DATE '2026-07-21', 'YYDDD')   AS julian5  FROM dual;  -- 26202

-- Parse a 7-digit ordinal code back to a date
SELECT TO_DATE('2026202', 'YYYYDDD') AS d FROM dual;   -- 2026-07-21

Astronomical Julian Day

The 'J' format is Oracle's built-in Julian Day Number — an integer matching the astronomical JDN — and it round-trips through TO_DATE:

-- 'J' = astronomical Julian Day NUMBER (integer), NOT day-of-year
SELECT TO_CHAR(DATE '2026-07-21', 'J') AS jdn FROM dual;   -- 2461243

-- Parse a Julian Day Number back to a calendar date
SELECT TO_DATE('2461243', 'J') AS d FROM dual;            -- 2026-07-21

PostgreSQL

Day of year and ordinal codes

Use EXTRACT(DOY FROM ...) for the numeric day-of-year, or to_char with 'DDD', 'YYYYDDD' and 'YYDDD' for the padded codes:

-- Day of year and ordinal codes
SELECT EXTRACT(DOY FROM DATE '2026-07-21')   AS doy;      -- 202
SELECT to_char(DATE '2026-07-21', 'DDD')     AS doy_str;  -- 202
SELECT to_char(DATE '2026-07-21', 'YYYYDDD') AS julian7;  -- 2026202
SELECT to_char(DATE '2026-07-21', 'YYDDD')   AS julian5;  -- 26202

-- Parse a 7-digit ordinal code back to a date
SELECT to_date('2026202', 'YYYYDDD') AS d;   -- 2026-07-21

Astronomical Julian Day

PostgreSQL also supports the 'J' pattern for the Julian Day Number. Plain date subtraction (which returns an integer number of days) gives the same result:

-- PostgreSQL supports the 'J' Julian-Day pattern directly
SELECT to_char(DATE '2026-07-21', 'J')::int AS jdn;   -- 2461243
SELECT to_date('2461243', 'J') AS d;                  -- 2026-07-21

-- Or with plain date arithmetic (day subtraction returns whole days)
SELECT (DATE '2026-07-21' - DATE '2000-01-01') + 2451545 AS jdn;  -- 2461243

MySQL

Day of year and ordinal codes

DAYOFYEAR() gives the 1-366 number, LPAD zero-pads it, and MAKEDATE(year, dayofyear) converts a code back to a date:

-- Day of year (1-366)
SELECT DAYOFYEAR('2026-07-21') AS doy;   -- 202

-- YYYYDDD, the 7-digit ordinal code
SELECT CONCAT(YEAR('2026-07-21'),
              LPAD(DAYOFYEAR('2026-07-21'), 3, '0')) AS julian7;  -- 2026202

-- YYDDD, the 5-digit code
SELECT CONCAT(LPAD(YEAR('2026-07-21') % 100, 2, '0'),
              LPAD(DAYOFYEAR('2026-07-21'), 3, '0')) AS julian5;  -- 26202

-- Parse a year + day-of-year back to a date
SELECT MAKEDATE(2026, 202) AS d;   -- 2026-07-21

Astronomical Julian Day

MySQL has no Julian-Day function either. The safest option is the same DATEDIFF-from-2000 approach; TO_DAYS() works too with a fixed offset. Both give the integer JDN — add the time of day for a fractional JD:

-- Integer Julian Day Number via date arithmetic
SELECT DATEDIFF('2026-07-21', '2000-01-01') + 2451545 AS jdn;  -- 2461243

-- TO_DAYS counts days since year 0; the constant aligns it to the JDN
SELECT TO_DAYS('2026-07-21') + 1721060 AS jdn;   -- 2461243

Related

• Julian date converter
• Julian Day: definition and history
• The same conversions in Python
• The same conversions in JavaScript