JD Edwards / IBM iSeries DB2 Julian Date / Calendar Date SQL Conversions

Question

There are still many legacy applications in use from the previous century running on the IBM iSeries. e.g. JD Edwards. Date fields are often stored in a variety of formats collectively and commonly called Julian Date. These formats are a combination of the year and the day of the year and are more accurately known as Ordinal Date today.

A specific Julian Date format used extensively in the JD Edwards ERP application database is a 6 digit format (YYYDDD) consisting of a 3 digit year (expressed as the number of years since 1900) and 3 digits (with leading zeros) for the day of the year. Having an clear and concise method to convert between these two formats in SQL is very helpful.

Here's a solution that IBM provides for Converting Julian Dates to Calendar Dates:

Select date (days(concat(cast(integer(1900000+"SDIVD") /1000 as Char(4)),'-01-01'))+mod(integer(1900000+" SDIVD"),1000)-1)

Ouch. My head hurts...

I'm hoping there is a simpler and easier way to convert either way using only IBM iSeries DB2 SQL syntax?

Side note: if you have a calendar table (a table with a range of dates and data derived off of them, such as day-of-week, fiscal period, etc), this would be the place to put such data. It would mean a `JOIN`, but may allow better performance since the column could be indexed. — Clockwork-Muse, Nov 18 '19 at 18:52

Peter · Answer 1 · 2021-10-01T15:31:20.197

I am not sure what you are using to display the data, but the limitation of

dates inclusively between 1940 and 2039

is tied to your session settings and should not return NULL, but rather a calculation error usually represented by '++++++++'. Nulls are usually represented as a single dash '-'. If you change your session settings to display the date in ISO format, rather than MDY format, the dates outside of the range should display normally. Relevant IBM article about String representations of datetime values. I do not know why this setting affects even scalar functions like DAYOFYEAR, but it does. (Edit: After submittig a bug report to IBM they released PTFs to address this specific issue for DAYOFYEAR SI77129 (7.3) and SI77130 (7.4) )

I can run

SELECT DATE(CHAR(140001 + 1900000)) FROM sysibm.sysdummy1;

and get

2040-01-01

For converting from date to long julian and subsequently to your ordinal date, I found a shorter version:

SELECT INT(TO_CHAR('2019-10-18','YYYYDDD'))-1900000 FROM sysibm.sysdummy1;

Your shorter version is awesome. Thank you! – rtev Jan 13 '20 at 15:17 — rtev, Jan 13 '20 at 15:17

rtev · Accepted Answer · 2019-11-18T16:20:50.323

For IBM i Db2 (Version 7.2) I've settled on the following two methods for conversion between Julian Dates and Calendar Dates:

-- Julian to Db2 Date (Method 1.0)
DATE(CAST(120050 + 1900000 AS CHAR(7)))

and

-- Db2 Date to Julian (Method 2.0)
1000 * (YEAR(DATE('02/19/2020')) - 1900) + DAYOFYEAR(DATE('02/19/2020'))

Update. I've found the first method can be shortened to:

-- Julian to Db2 Date (Method 1.1)
DATE(CHAR(120050 + 1900000))

Here some examples of them in use in a SQL statement (along with the IBM approach):

SELECT
    TEST_DATA."DATE"
    , TEST_DATA."JULIAN"
    , YEAR(TEST_DATA."DATE") AS "YEAR"
    , DAYOFYEAR(TEST_DATA."DATE") AS "DAYOFYEAR"
    , DATE(DAYS(CONCAT(CAST(INTEGER(1900000 + TEST_DATA."JULIAN") / 1000 AS CHAR(4)), '-01-01')) + MOD(INTEGER(1900000 + TEST_DATA."JULIAN"), 1000) - 1) AS "METHOD_IBM"
    , DATE(CAST((TEST_DATA."JULIAN" + 1900000) AS CHAR(7))) AS "METHOD_1.0"
    , DATE(CHAR(TEST_DATA."JULIAN" + 1900000)) AS "METHOD_1.1"
    , 1000 * (YEAR(TEST_DATA."DATE") - 1900) + DAYOFYEAR(TEST_DATA."DATE") AS "METHOD_2.0"
FROM
    TABLE
    (
        VALUES
              (DATE('12/31/1938'), 039365)
            , (DATE('12/31/1939'), 039365)
            , (DATE('01/01/1940'), 040001)
            , (DATE('02/19/2020'), 120050)
            , (DATE('2020-01-01'), 119366)
            , (DATE('2039-01-01'), 139001)
            , (DATE('2039-12-31'), 139365)
            , (DATE('2040-01-01'), 140001)
            , (DATE('2041-01-15'), 141015)
    )
    AS TEST_DATA("DATE", "JULIAN")
;

The highlighted record was suggested by another answer:

Try IBM's version and yours with 119366...

I don't know if there is a "standard" that speaks to this particular value but I would say that 119366 is not a valid Julian date because 2019 is not a leap year and has only 365 days. I think allowing "overflow" into succeeding years is a unfavorable approach nor have I seen any legacy applications store dates in such a manner. Generally speaking I want my queries to highlight irregularities in the data rather than masking them.

Please note that at least some of the database scalar functions only calculate for dates inclusively between 1940 and 2039. I've included some dates outside this range to demonstrate the odd behavior. I'm not sure where the behavior is documented by IBM but nothing is mentioned in the documentation for the scalar DAYOFYEAR function.

score 0 · Answer 3 · answered Nov 14 '19 at 23:50

Try IBM's version and yours with 119366...

Which ever conversion method you choose, I'd wrap it in a User Defined Function (UDF).

Doing so opens up the possible of custom error handling, for instance with numeric fields storing dates. All zeros might mean "not set" and could be replaced by null, whereas all 9's should be replaced by 9999-12-31.

Using a UDF means you could consider using a different language to do the conversion, RPG for instance. There's an existing open source package of date conversion UDFs known as iDate

The other option to consider, would be a "dates"/"calendar"/"date dimension" table. Very handy to have around, not just for date conversion. A google search will turn up lots of info applicable to any DB.

For IBM i specific examples, check out chapter 5 of the Redbook IBM DB2 Web Query for i: The Nuts and Bolts (Note if you happen to have Db2 for i Web Query installed, there's a script to create such a dates table.)

For example, a date dimension table might include the following columns:
- Julian legacy date, which is used when joining to files that use Julian legacy dates
- Packed decimal(8,0) legacy date, which is used when joining to files that use Packed decimal(8,0) legacy dates
- Character(8) legacy date, which is used when joining to files that use Character(8) legacy dates
- Date (a true DB2 date field)
- Fiscal year - Fiscal quarter
- Day of the week (Monday, Tuesday, and so on)
- Month of the year (January, February, and so on)
- Season (spring, summer, autumn, and winter)
- Same day (of the week) last year
- Week ending date
- Week of the year
- Super Bowl Sunday flag (Y or N)
- Day before a holiday flag (Y or N)
- Day after a holiday flag (Y or N)
- Full moon flag (Y or N)

Thank you for your answer. I've updated my answer with an example of the Julian date you suggest here. In my current situation I don't have access to create User Defined Functions that would be accessible by other users subsequently and the date dimension table that is available to me does not contain a Date (true DB2 Date) field nor is the team that maintains it willing to add a new field. I'm sorry my requirements were not clearer when I stated "do this in IBM iSeries DB2 SQL". I'll update my question to make this clearer. Thank you again for your response. — rtev, Nov 15 '19 at 18:08

JD Edwards / IBM iSeries DB2 Julian Date / Calendar Date SQL Conversions

3 Answers3