Search code examples
sqldb2ibm-midrangejdedwards

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

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. enter image description here 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?

Solution

  • 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")
;

    enter image description here

    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.