Determining the Ordinal date with SQL

A friend asked me if there was a simple way to calculate the "Julian" date when retrieving dates from a DDL table or DDS file with a date type field.

Before I continue I need to describe the difference between the Julian and the Ordinal dates. While we all call a date in YYYYDDD a "Julian" date, that is not correct. The Julian date is a count of days since January 1, 4713 BCE. I found this helpful link describing why it is this date. The correct name for the YYYYDDD date is the Ordinal date, which is recognized by the ISO 8601 standard.

Db2 for i includes a Julian day scalar function. The statement below show me using it with the date June 1, 2026:

01 VALUES JULIAN_DAY('2026-06-01')

The result is:

00001 ------- 2461193

My friend wanted the Ordinal date. I can get that using the TO_CHAR scalar function:

01 VALUES (CURRENT_DATE, TO_CHAR(CURRENT_DATE, 'YYYYDDD'))

Which gives me:

00001 00002 ---------- ------- 2026-06-01 2026152

But, he only wants the three long year, CYY, rather than the four. I can modify the above:

01 VALUES (CURRENT_DATE, TO_CHAR(CURRENT_DATE, 'YYYDDD'))

I changed the second parameter in the TO_CHAR to be "YYY".

Which gives me:

00001 00002 ---------- ------ 2026-06-01 026152

It has given me a three long year, (2)026, which was not what I expected.

Using the following produced an error:

01 VALUES (CURRENT_DATE, TO_CHAR(CURRENT_DATE, 'CYYDDD'))

Therefore, I need to adopt another approach. I can use two other scalar functions and combine them to get the date formatted in the way wanted.

01 VALUES (YEAR(CURRENT_DATE), DAYOFYEAR(CURRENT_DATE))

The YEAR scalar function will extract the year from a date, and DAYOFYEAR the number of days in the year.

The results from this are:

00001 00002 ------ ------ 2026 152

I can take that and modify it by subtracting 1900 from the year, and then concatenate ( || ) with the number of days:

01 VALUES (YEAR(CURRENT_DATE) - 1900) || DAYOFYEAR(CURRENT_DATE)

And this gives me the format for the year my friend wanted:

00001 ------ 126152

The proof that this works they way needed is to create a SQL statement that will format the dates from DDL table or DDS file. I have a DDL table that has a list of names and when their birthday is this year. I can use that file to test that my examples, above, will work:

01 SELECT TEST_NAME,TEST_DATE, 02 TO_CHAR(TEST_DATE, 'YYYYDDD'), 03 (YEAR(TEST_DATE) - 1900) || DAYOFYEAR(TEST_DATE) 04 FROM TESTTABLE 05 ORDER BY 1

Line 1: Person's name and the date of their birthday this year.

Line 2: Convert the date to a Ordinal date using the TO_CHAR scalar function.

Line 3: This is the same as the last example I gave above.

Line 4: Get the data from the DDL table TESTTABLE.

Line 5: Order the results by name.

TEST_NAME TEST_DATE 00001 00002 --------- ---------- ------- ------ ANGELO 2026-02-21 2026052 12652 DAVID 2026-01-31 2026031 12631 HARRY 2026-01-16 2026016 12616 MARIA 2026-06-30 2026181 126181 WESLEY 2026-09-24 2026267 126267

Notice that some of the are a character shorter than others. This happens as the DAYOFYEAR suppresses any leading zeroes.

I need to add a zero to result of DAYOFYEAR to bring it up to three long. I can use the LPAD scalar function to achieve this. What I need to do is to pad the result from DAYNUMBER with a zero to make it three long. I modify the previous SQL statement to include the LPAD:

01 SELECT TEST_NAME,TEST_DATE, 02 TO_CHAR(TEST_DATE, 'YYYYDDD'), 03 (YEAR(TEST_DATE) - 1900) || LPAD(DAYOFYEAR(TEST_DATE),3,0) 04 FROM TESTTABLE 05 ORDER BY 1

Line 4: I have the LPAD for the DAYOFYEAR.

The results are:

TEST_NAME TEST_DATE 00001 00002 --------- ---------- ------- ------ ANGELO 2026-02-21 2026052 126052 DAVID 2026-01-31 2026031 126031 HARRY 2026-01-16 2026016 126016 MARIA 2026-06-30 2026181 126181 WESLEY 2026-09-24 2026267 126267

I showed my friend that I could embed the SQL statement into a RPG program and I the same results:

01 **free 02 dcl-ds Data qualified dim(*auto : 10) ; 03 Name char(20) ; 04 Date date(*iso) ; 05 Date1 packed(7 : 0) ; 06 Date2 packed(6 : 0) ; 07 end-ds ; 08 dcl-s Rows uns(3) inz(%elem(Data : *max)) ; 09 exec sql SET OPTION DATFMT = *ISO ; 10 exec sql DECLARE C0 CURSOR FOR 11 SELECT TEST_NAME,TEST_DATE, 12 TO_CHAR(TEST_DATE, 'YYYYDDD'), 13 (YEAR(TEST_DATE) - 1900) || LPAD(DAYOFYEAR(TEST_DATE),3,0) 14 FROM TESTTABLE 15 ORDER BY 1 16 FOR READ ONLY ; 17 exec sql OPEN C0 ; 18 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 19 exec sql CLOSE C0 ; 20 *inlr = *on ;

Lines 2 – 7: This is an auto extending data structure array that will contain a maximum of ten elements. The data structure contains the following subfields:

• Name: Name of the person
• Date: Their birthday in 2026
• Date1: Numeric equivalent of the date as YYYYDDD
• Date2: Numeric equivalent of the date as CYYDDD

Line 8: I am using the %ELEM Built in Function, BiF, to initialize this variable with the maximum elements the data structure array has.

Line 9: I use the SET OPTION SQL statement to instruct the program the format I want the date in.

Lines 10 - 16: Definition of the cursor that will be used to retrieve the results from the table. Notice, on line 16, I have defined cursor as input only.

Lines 17: Open the cursor.

Line 18: Fetch up to the maximum number of elements for the data structure array into the array.

Line 19: Close the cursor.

After compiling the program, I place a debug breakpoint on line 20. When I check the data structure array I see that all the dates have been formatted the way I wanted.

> data DATA.NAME(1) = 'ANGELO ' DATA.DATE(1) = '2026-02-21' DATA.DATE1(1) = 2026052. DATA.DATE2(1) = 126052. DATA.NAME(2) = 'DAVID ' DATA.DATE(2) = '2026-01-31' DATA.DATE1(2) = 2026031. DATA.DATE2(2) = 126031. DATA.NAME(3) = 'HARRY ' DATA.DATE(3) = '2026-01-16' DATA.DATE1(3) = 2026016. DATA.DATE2(3) = 126016. DATA.NAME(4) = 'MARIA ' DATA.DATE(4) = '2026-06-30' DATA.DATE1(4) = 2026181. DATA.DATE2(4) = 126181. DATA.NAME(5) = 'WESLEY ' DATA.DATE(5) = '2026-09-24' DATA.DATE1(5) = 2026267. DATA.DATE2(5) = 126267.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.