OracleFree generatorPDF, Excel, Word exportOracle JD Edwards World

JD Edwards Julian Date (CYYDDD) to SQL Date Converter

JD Edwards files store dates as a Julian number in CYYDDD format, so 126293 means the 293rd day of 2026. This generator builds the SQL to read those values as real dates and to filter by an ordinary date range.

Fill in

Library

Table (file)

Julian date column

Date columns in JDE files usually end in J.

Output column name

Range start

Range end

Sample JDE value to decode

Generated code

Download:

Convert JDE Julian to a real date

SELECT SDDRQJ AS JDE_JULIAN,
       DATE(TRIM(CHAR(1900 + INT(SDDRQJ / 1000))) CONCAT '-01-01')
         + (MOD(INT(SDDRQJ), 1000) - 1) DAYS AS REQUEST_DATE
FROM PRODDTA.F4211
WHERE SDDRQJ > 0
FETCH FIRST 100 ROWS ONLY;

Convert a real date to JDE Julian

SELECT (YEAR(DATE('2026-01-01')) - 1900) * 1000 + DAYOFYEAR(DATE('2026-01-01')) AS JDE_FROM,
       (YEAR(DATE('2026-12-31')) - 1900) * 1000 + DAYOFYEAR(DATE('2026-12-31')) AS JDE_TO
FROM SYSIBM.SYSDUMMY1;

Filter a table by a date range

SELECT *
FROM PRODDTA.F4211
WHERE SDDRQJ BETWEEN
      (YEAR(DATE('2026-01-01')) - 1900) * 1000 + DAYOFYEAR(DATE('2026-01-01'))
  AND (YEAR(DATE('2026-12-31')) - 1900) * 1000 + DAYOFYEAR(DATE('2026-12-31'));

Decode one sample value

SELECT 126293 AS JDE_JULIAN,
       DATE(TRIM(CHAR(1900 + INT(126293 / 1000))) CONCAT '-01-01')
         + (MOD(126293, 1000) - 1) DAYS AS REAL_DATE
FROM SYSIBM.SYSDUMMY1;

JD Edwards stores dates as a six digit CYYDDD number: century (0 for 1900s, 1 for 2000s), two digit year, day of year. The filter in block three works on the stored number, so Db2 can use an index on the date column. Test on a copy first, and check the column really holds a JDE date (a value of 0 means empty).

Report on JD Edwards World data in plain English

Optional: a read-only assistant that handles date conversions and file joins for you and shows the SQL.

Optional. The tool and every export work without this. No spam.

How to use it

Enter your data library, table and the date column, then choose a start and end date. Copy the first block to see readable dates beside the raw value. Copy the third block to filter a table by a date range. The last block lets you decode a single value from a screen or a report. Run in STRSQL or Run SQL Scripts, always against a test copy or with a row limit first.

How the CYYDDD format works

C is the century counted from 1900, so 0 is 19xx and 1 is 20xx. YY is the two digit year and DDD is the day of the year from 001 to 366. The year is 1900 plus the first three digits, so 126 becomes 2026. Day 293 of 2026 is October 20. The converter adds 292 days to January 1 because day 1 is January 1 itself. Leap years are handled by Db2 date arithmetic.

126293 is 2026-10-20
099365 is 1999-12-31
0 means no date entered
Year = 1900 + first three digits

Why filter on the number, not the converted date

If you convert every row to a date and then compare, Db2 has to calculate each row and cannot use an index on the date column. Converting your range limits into Julian numbers once and comparing them to the stored value lets the optimizer use the index. That is why the range block converts the limits, not the column.

Common mistakes

Forgetting that zero means blank produces errors such as an invalid date for rows without a date. Using the wrong century digit gives a date 100 years off. Mixing up JDE World Julian dates with the Julian Day Number used in astronomy, or with the YYDDD format used by some other systems, gives wrong answers. Check one known record by hand before trusting a report.

Frequently Asked Questions

What does the C in CYYDDD stand for?

It is the century offset from 1900. A 0 means the date is in the 1900s and a 1 means the 2000s. So 126293 is year 1900 plus 126, which is 2026, and day 293 of that year, which is October 20.

How do I handle a zero or blank JDE date?

A value of 0 means no date was entered. Filter these rows out with WHERE column > 0 before converting, or use a CASE expression that returns NULL for zero, otherwise the conversion produces nonsense dates.

Do JD Edwards EnterpriseOne files use the same format?

E1 on Oracle, SQL Server and Db2 stores dates as Julian values in many tables but often as real date types in newer ones. This tool targets World files on Db2 for i. For E1, check the column data type first.

Can I use this in a view or logical file?

Yes. You can create an SQL view with the converted date column and use it for reporting tools. Keep the original Julian column as well so keyed access and JDE programs keep working without changes.

If you migrate or report from JD Edwards World data regularly, Netray can build a private assistant that handles these conversions and shows the SQL it ran.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Oracle tools

See all

Back to All Tools