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
- Calendar date
- Padded CYYDDD
- Standard Julian (YYYYDDD)
- Day of year
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
| CYYDDD | Calendar date | Why |
|---|---|---|
| 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.
| Month | Common year | Leap year |
|---|---|---|
| January 1 | 001 | 001 |
| February 1 | 032 | 032 |
| March 1 | 060 | 061 |
| April 1 | 091 | 092 |
| May 1 | 121 | 122 |
| June 1 | 152 | 153 |
| July 1 | 182 | 183 |
| August 1 | 213 | 214 |
| September 1 | 244 | 245 |
| October 1 | 274 | 275 |
| November 1 | 305 | 306 |
| December 1 | 335 | 336 |
So in 2026, September 1 is 126244 and
December 31 is 126365.
Related formats
- Standard Julian date converter:
the
YYYYDDDandYYDDDforms used on packaging and in most IT systems - Julian dates in SQL: day-of-year and JDE conversions for nine databases
- Bulk converter — paste a whole column of values at once
- What is a Julian date? — why the term means several different things
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.