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
New to JD Edwards EnterpriseOne? Start with the JD Edwards EnterpriseOne guide and toolkit, or plan with Training Path Builder, Upgrade Path Planner, ERP Table and Field Explorer.
Keep exploring
Free tools
- JD Edwards Julian Date (CYYDDD) SQL ConverterBuild SQL that converts the CYYDDD Julian dates stored in JD Edwards files into real dates, converts a date back, and filters by date range.
- JD Edwards F0911 General Ledger SQL GeneratorFill in schema, company, ledger type and a Julian date range to get ready-to-run SQL for F0911 detail and an account summary.
- JD Edwards F4801 Work Order SQL GeneratorBuild SQL for open JDE work orders by branch plant, status and date, plus the parts list for one work order.
ERP fixes and how-tos
- How to Add a Private AI Layer to JD Edwards EnterpriseOne On-PremAdd a private AI layer to JD Edwards EnterpriseOne: AIS data service, Orchestrator, Security Workbench mapping, a local model and human approval.
- JDE Work Order Routing: The F3112 Routing File and How It Attaches to a Work OrderHow JD Edwards work order routing works: the F3112 routing file, how the F3003 master routing copies onto a work order, setup steps and SQL to inspect it.
- JD Edwards table conversion: how it works and how to run oneWhat a JD Edwards table conversion does to a JDE table's structure and data, when it runs in a package build, and how to run or design one manually.
Runs with your ERP
- Oracle CPQ and JD Edwards EnterpriseOne: quote to sales orderHow Oracle CPQ integrates with JD Edwards EnterpriseOne: customer data into quotes, sales orders created from quotes, and status back, with setup and pitfalls.
- Vertex Tax Connectors with JD Edwards EnterpriseOneHow Vertex calculates sales, use and VAT tax for JD Edwards EnterpriseOne and World, plus Oracle EBS and Cloud ERP: integration model, flows, setup, pitfalls.
- Cleo Integration Cloud with JD Edwards: EDI Connector and Migration PathCleo Integration Cloud has a prebuilt JD Edwards EnterpriseOne connector for EDI. See data flows, setup steps, pitfalls and Oracle Fusion migration notes.
AI on your ERP
- AI for JD Edwards Inventory Management and Shop Floor ManufacturingAI on JD Edwards EnterpriseOne inventory and manufacturing: item ledger, work orders, MRP messages and cost variances, answered in plain English on your own servers.
- AI for JD Edwards EnterpriseOne Manufacturing, Without Leaving the F-Tables ExposedGrounded AI over JD Edwards EnterpriseOne Manufacturing: work orders, routings, and Orchestrator data, answered on-prem without sending F-table data to a public model.
Integrations
- How to connect Power BI to JD Edwards WorldConnect Power BI to JD Edwards World on IBM i: ODBC and gateway setup, a staging layer for Julian dates, key manufacturing dashboards, and natural-language Q&A.
- How to add AI agents to JD Edwards EnterpriseOneHow to add AI agents to JD Edwards EnterpriseOne: Orchestrator and AIS as the tool layer, read-only first, human approval on writes, on-prem models.