JD Edwards Julian Date Converter (CYYDDD)
JD Edwards EnterpriseOne keeps dates in its database as a six digit Julian number called CYYDDD. This converter turns a calendar date into that number and shows how to turn it back to a real date in SQL.
Your numbers
The value is the number of days in the months before the chosen one in a non-leap year.
Your results
Check the day against the month length yourself, for example 31 April is not rejected. JDE date columns of 0 mean no date.
Reporting views for JDE
Optional: talk through read-only views that decode dates and amounts for your analysts.
Optional. The tool and every export work without this. No spam.
How to use it
Pick the year, month and day. The result is the value you can paste into a WHERE clause against columns such as GLDGJ (GL date) in F0911 or WADRQJ (request date) in F4801. For example 19 October 2026 is day 292 of the year, giving 126292.
The CYYDDD structure
C is the century indicator, calculated as the year divided by 100 minus 19, so 0 for 1900 to 1999 and 1 for 2000 to 2099. YY is the last two digits of the year and DDD is the day of the year from 001 to 366. Put together it equals (year minus 1900) times 1000 plus the day of year, which is the formula this calculator uses.
- 1 January 2000 is 100001
- 31 December 1999 is 099365
- A stored value of 0 means no date
Decoding in SQL
In SQL Server use DATEADD(DAY, (col % 1000) - 1, DATEFROMPARTS(1900 + col / 1000, 1, 1)). In Oracle use TO_DATE(TO_CHAR(1900 + TRUNC(col / 1000)) || LPAD(MOD(col, 1000), 3, '0'), 'YYYYDDD'). In DB2 for i, the DATE function with the Julian format works similarly. Guard against zero values with a CASE expression.
Common mistakes
Forgetting that the century digit changed at 2000, comparing a Julian number to a formatted date string, and using BETWEEN on dates that span a year end without considering both years. Leap years add a day after February, which this tool handles.
Frequently Asked Questions
What is the JDE date 126001?
It is 1 January 2026. The 126 is 2026 minus 1900 and the 001 is the first day of the year. Likewise 126365 is 31 December 2026 because 2026 is not a leap year.
Why is there a century digit?
JD Edwards designed the format around the year 2000 problem. The century digit allows years from 1900 to 2899 in six digits while staying compatible with older two digit year data.
Can I change the JDE date format in the database?
No. The stored Julian format is part of the data dictionary type JDEDATE. Applications convert it to your user display format. Report writers must convert it themselves in SQL.
How are times stored?
Time columns such as the update time are usually six digit integers in HHMMSS form, for example 143005 for 14:30:05. They are separate from the date column.
If you routinely translate JDE dates and amounts for reports, we can build read-only reporting views or a query assistant that does it for you.
Related Tools
JD Edwards F0911 General Ledger SQL Generator
Fill in schema, company, ledger type and a Julian date range to get ready-to-run SQL for F0911 detail and an account summary.
Developer ToolsJD Edwards F4801 Work Order SQL Generator
Build SQL for open JDE work orders by branch plant, status and date, plus the parts list for one work order.
OracleJD Edwards EnterpriseOne Core Table Reference
Searchable list of the JD Edwards EnterpriseOne tables you query most often, with column prefix and the key fields to filter on.
More Oracle tools
See all- JD Edwards Document Types Reference (OP, SO, OV, PV)
- JD Edwards UBE Job Scheduling Worksheet
- JD Edwards Table Reference (F4211, F4311, F0101, F4101, F4102)
- JD Edwards Tools Release Upgrade Checklist
- JD Edwards Orchestrator Design Template
- JD Edwards Security Workbench Worksheet
- JD Edwards Environment Refresh Runbook Template
- JD Edwards UBE Runtime Log Worksheet
- JD Edwards Next Numbers Capacity Worksheet