Oracle JD Edwards · AS/400

JD Edwards Julian Date Converter

JD Edwards stores dates as CYYDDD — a century digit, a two-digit year, and the day of year. That is not the same as the usual YYYYDDD Julian date, so a normal converter will decode it wrongly. Today is 2026321 in standard form and 126321 in JDE form.

Usually 6 digits (126260); 1900s dates have 5 (99365)

Pick a date to get its CYYDDD value

How CYYDDD works

The format packs three things into one number:

  • C — centuries since 1900. 0 = 1900s, 1 = 2000s, 2 = 2100s.
  • YY — the year within that century, 00–99.
  • DDD — the day of the year, 001–366.

So 126260 reads as century 1 → 2000s, year 26 → 2026, day 260 → 17 September. The full year is 1900 + (C × 100) + YY.

Worked examples

CYYDDDCalendar dateWhy
126260 September 17, 2026 C=1 (2000s), YY=26, DDD=260
125001 January 1, 2025 The first day of 2025
124366 December 31, 2024 2024 is a leap year, so day 366 exists
100001 January 1, 2000 The century digit rolls to 1
99365 December 31, 1999 Stored as a number, so the leading 0 is gone
5032 February 1, 1905 1900–1909 dates lose two leading zeros (005032)
0 No date JDE stores an empty date as 0

The five-digit trap

JD Edwards holds these values in numeric columns, not text. A date in the 1900s has C = 0, and a number has no leading zeros, so 099365 comes out of the database as 99365, and a date in 1900–1909 such as 005032 comes out as 5032. Pad any JDE date to six digits before you split it as text, or use the arithmetic formulas below, which never need padding. The converter above accepts all of these forms.

The quick way: add 1,900,000

Adding 1900000 turns any CYYDDD value into an ordinary 7-digit YYYYDDD Julian date: 126260 + 1900000 = 2026260, and 99365 + 1900000 = 1999365. Subtract it to go back. This is why every formula below starts with that number or with 1900 + value / 1000.

JDE dates in Excel

A1 holds a JDE date (number or text):
=DATE(1900+INT(A1/1000),1,MOD(A1,1000))        CYYDDD -> date (format the cell as a date)

A1 holds a date:
=(YEAR(A1)-1900)*1000+A1-DATE(YEAR(A1),1,0)    date -> CYYDDD

Skip empty JDE dates:
=IF(A1=0,"",DATE(1900+INT(A1/1000),1,MOD(A1,1000)))

Format the result cells as dates. More Excel conversions, including Power Query, are on the Excel page.

JDE dates in SQL

These examples read the order date (SDTRDJ) from the Sales Order Detail table F4211. The same expressions work on any JDE date column; replace proddta with your environment’s schema.

-- Oracle (JDE on Oracle Database)
SELECT TO_DATE(TO_CHAR(sdtrdj + 1900000), 'YYYYDDD') FROM proddta.f4211 WHERE sdtrdj > 0;
-- back:  TO_NUMBER(TO_CHAR(d, 'YYYYDDD')) - 1900000

-- SQL Server
SELECT DATEADD(day, CAST(sdtrdj AS int) % 1000 - 1,
               DATEFROMPARTS(1900 + CAST(sdtrdj AS int) / 1000, 1, 1))
FROM proddta.f4211 WHERE sdtrdj > 0;
-- back:  (YEAR(d) - 1900) * 1000 + DATEPART(dayofyear, d)

-- Db2 for IBM i (AS/400)
SELECT DATE(DIGITS(DEC(sdtrdj + 1900000, 7, 0))) FROM proddta.f4211 WHERE sdtrdj > 0;
-- back:  (YEAR(d) - 1900) * 1000 + DAYOFYEAR(d)

PostgreSQL, MySQL and SQLite versions are on the SQL page.

JDE time fields

JDE keeps the time of day in a separate numeric column (for example UPMT, time last updated) as HHMMSS without separators. A time before 10:00 has five digits or fewer: 9:05:00 is 90500.

JDE time 143005  ->  14:30:05
hours   = INT(t / 10000)        14
minutes = MOD(INT(t / 100), 100) 30
seconds = MOD(t, 100)            5

Excel: =DATE(1900+INT(A1/1000),1,MOD(A1,1000)) + TIME(INT(B1/10000),MOD(INT(B1/100),100),MOD(B1,100))

Day numbers for the first of each month

Use this table to read the DDD part at a glance. In a leap year (YY divisible by 4 in the 2000s, such as 124 or 128), every month from March on starts one day later.

MonthCommon yearLeap year
January 1001001
February 1032032
March 1060061
April 1091092
May 1121122
June 1152153
July 1182183
August 1213214
September 1244245
October 1274275
November 1305306
December 1335336

So in 2026, September 1 is 126244 and December 31 is 126365.

Related formats

Frequently asked questions

What does the C in CYYDDD mean?

It is the number of centuries since 1900. C=0 means the 1900s, C=1 means the 2000s, C=2 means the 2100s. So 126260 is century 1 (2000s), year 26, day 260: September 17, 2026. In general, year = 1900 + 100 × C + YY.

How do I convert a JDE Julian date to a regular date?

Add 1,900,000 to the JDE value. That turns CYYDDD into YYYYDDD (126260 becomes 2026260), which is year 2026, day 260: September 17, 2026. In Excel, =DATE(1900+INT(A1/1000),1,MOD(A1,1000)) does it in one step.

Why is my JDE date only 5 digits?

JD Edwards stores the value in a numeric column, so a 20th-century date with C=0 loses its leading zero: 099365 comes back as 99365. Dates in 1900 to 1909 lose two zeros (005032 becomes 5032). Pad to six digits, or use the arithmetic formulas on this page, which do not need padding.

Is the JDE Julian date the same as a normal Julian date?

No. A standard day-of-year Julian date is YYYYDDD (2026260) or YYDDD (26260). The JDE form is CYYDDD (126260), with a century digit instead of a full year. Feeding a JDE value into a normal converter gives the wrong answer.

What does a JDE date of 0 mean?

It means no date has been entered. Filter out zeros (WHERE col > 0) or turn them into NULL before converting, or they will come out as December 31, 1899 (1/0/1900 in Excel).

Which JDE tables use this format?

Every standard date column, for example the update date in F0101 (ABUPMJ), the G/L date in F0911 (GLDGJ) and the order date in F4211 (SDTRDJ). Any data item of the Date type is stored as CYYDDD.

How do I convert CYYDDD in SQL?

Add 1900000 to the value to get YYYYDDD, then convert that. On Oracle: TO_DATE(TO_CHAR(jdedate + 1900000), 'YYYYDDD'). The SQL section above has SQL Server and Db2 versions, and the SQL code page covers PostgreSQL, MySQL and SQLite.

Is JDE in astronomy the same thing?

No. In astronomy, JDE means Julian Ephemeris Date, the astronomical Julian Date measured in Terrestrial Time. It has nothing to do with JD Edwards CYYDDD dates.