Julian Date Conversion in Code: YYDDD, CYYDDD, JD and MJD in Python, JavaScript, SQL and Excel

#python #sql #javascript #datetime

"Julian date" means two different things, and both show up in real code. A batch code on a food carton, a mainframe file or a JD Edwards table usually means an ordinal date: the year plus the day of the year (26294 = day 294 of 2026 = October 21, 2026). An astronomer, a satellite pass predictor or a FITS header means the Julian Date (JD): a continuous count of days since noon UTC on January 1, 4713 BC in the proleptic Julian calendar (2461334.5 = 00:00 UTC on October 21, 2026).

Mixing the two up is a common source of bugs. This post collects the conversions for both, with a worked example you can test against.

The formats at a glance

Format	Example for 2026-10-21	Where you see it
YYDDD	`26294`	Food and pharma lot codes, older mainframe files
YYYYDDD	`2026294`	Logs, batch systems, ISO 8601 "ordinal date" (`2026-294`)
YDDD	`6294`	Military document numbers, some product codes
CYYDDD (JDE)	`126294`	Oracle JD Edwards; C = centuries after 1900
JD	`2461334.5` (midnight UTC)	Astronomy, ephemerides, spacecraft data
MJD	`61334.0`	Satellite and timing work; MJD = JD âˆ’ 2400000.5

Two reference points are worth memorizing: the Unix epoch (1970-01-01 00:00 UTC) is JD 2440587.5, and J2000 (2000-01-01 12:00) is JD 2451545.0.

Python

from datetime import date, datetime, timedelta, timezone

d = date(2026, 10, 21)

# Ordinal / "Julian" day-of-year codes
d.strftime("%j")        # '294'
d.strftime("%y%j")      # '26294'   (YYDDD)
d.strftime("%Y%j")      # '2026294' (YYYYDDD)

# Back to a date
datetime.strptime("26294", "%y%j").date()    # 2026-10-21
datetime.strptime("2026294", "%Y%j").date()  # 2026-10-21

# Astronomical JD at 00:00 UTC
jd = d.toordinal() + 1721424.5               # 2461334.5
mjd = jd - 2400000.5                         # 61334.0

# JD for any aware datetime
def to_jd(dt: datetime) -> float:
    return dt.timestamp() / 86400 + 2440587.5

to_jd(datetime(2026, 10, 21, 12, tzinfo=timezone.utc))  # 2461335.0

# JD Edwards CYYDDD
def jde_to_date(n: int) -> date:
    c, yy, ddd = n // 100000, (n // 1000) % 100, n % 1000
    return date(1900 + 100 * c + yy, 1, 1) + timedelta(days=ddd - 1)

def date_to_jde(d: date) -> int:
    return (d.year - 1900) * 1000 + d.timetuple().tm_yday

jde_to_date(126294)   # 2026-10-21
date_to_jde(d)        # 126294

date.toordinal() counts days from January 1 of year 1 in the proleptic Gregorian calendar, so adding 1721424.5 gives the JD at midnight. Use a timezone-aware datetime for to_jd; a naive one is treated as local time by timestamp().

JavaScript

// JD from a Date (Date stores milliseconds since the Unix epoch, in UTC)
const toJD = (date) => date.getTime() / 86400000 + 2440587.5;
toJD(new Date(Date.UTC(2026, 9, 21, 12)));   // 2461335

// Day of year, computed in UTC so DST changes can't shift it
const dayOfYear = (y, m, d) => (Date.UTC(y, m, d) - Date.UTC(y, 0, 0)) / 86400000;
dayOfYear(2026, 9, 21);   // 294 (months are 0-based)

// YYDDD -> Date (assumes 20xx)
const fromYYDDD = (s) =>
  new Date(Date.UTC(2000 + Number(s.slice(0, 2)), 0, Number(s.slice(2))));
fromYYDDD("26294").toISOString();   // '2026-10-21T00:00:00.000Z'

Date.UTC(y, 0, n) rolls day n of January forward through the year, which is what makes the one-line decoder work.

SQL

-- SQL Server
SELECT DATEPART(dayofyear, '2026-10-21');                           -- 294
DECLARE @n int = 26294;
SELECT DATEADD(day, @n % 1000 - 1, DATEFROMPARTS(2000 + @n / 1000, 1, 1));  -- 2026-10-21

-- PostgreSQL
SELECT EXTRACT(DOY FROM DATE '2026-10-21');      -- 294
SELECT to_date('2026294', 'YYYYDDD');           -- 2026-10-21
SELECT to_char(DATE '2026-10-21', 'J');         -- 2461335

-- Oracle
SELECT TO_CHAR(DATE '2026-10-21', 'DDD') FROM dual;      -- 294
SELECT TO_DATE('2026294', 'YYYYDDD') FROM dual;         -- 2026-10-21
SELECT TO_CHAR(DATE '2026-10-21', 'J') FROM dual;        -- 2461335

Note the J results. PostgreSQL and Oracle return the integer day number for the calendar date, which matches the astronomical JD at noon, not at midnight. PostgreSQL's documentation says so directly: it treats a Julian Date as running from local midnight to local midnight. If you need the fractional astronomical value, subtract 0.5 and add the time of day yourself, or do the arithmetic in UTC+12 as the PostgreSQL docs show.

Excel

With a date in A1:

Goal	Formula	Result for 2026-10-21
Day of year	`=A1-DATE(YEAR(A1),1,0)`	294
YYDDD text	`=TEXT(A1,"yy")&TEXT(A1-DATE(YEAR(A1),1,0),"000")`	26294
YYDDD in `B1` â†’ date	`=DATE(2000+INT(B1/1000),1,MOD(B1,1000))`	2026-10-21
JD at midnight	`=A1+2415018.5`	2461334.5

DATE(year, 1, 294) works because Excel carries an out-of-range day into the following months. The +2415018.5 offset assumes the default 1900 date system and dates after February 28, 1900, where Excel's historical leap-year bug no longer matters.

The traps

Leap years. Day 060 is March 1 in 2026 but February 29 in 2024. Never hard-code a month table without a leap-year check; let the date library do it.
The century. 26294 could be 1926 or 2026. YYDDD and YDDD codes only make sense with outside context, such as a shelf life or a manufacturing window. Decide on a pivot year and document it.
Noon vs. midnight. The astronomical JD changes at 12:00 UTC. A JD ending in .5 is midnight. Database J formats and many "Julian day number" functions return the integer for noon.
Time zones. A day-of-year code is a local calendar date. A JD is UTC. Convert before you compare.
Julian calendar â‰ Julian Date. The Julian calendar is the pre-Gregorian calendar, currently 13 days behind. Neither date format above uses it for modern dates.

Checking your output

When you are unsure about a result, compare it with an independent converter. The U.S. Naval Observatory and NASA JPL both publish one for the astronomical JD (see sources 1 and 4). For ordinal codes, a printed day-of-year table is the quickest sanity check.

DEV Community