Legacy databases on the IBM i stored dates in numeric (or character) fields. Doing validation or arithmetic on such fields was complex. IBM finally added a true date type to the database as Y2K approached.

After Y2K, many of these dates still existed in databases. I developed these SQL UDFs to convert a legacy date to a true date, largely so I could do date arithmetic. For example:

SELECT ...  FROM CUSTMAST WHERE DATE_YMD(DUEDATE) <= CURDATE() - 9O DAYS

The most common formats where month-day-year (in the USA) and year-month-day. Often there was no century included. There was also an IBM sanctioned format, CYMD, where the C was a 1-digit century, with 0 meaning 19, and 1 meaning 20.

Development

What these functions do can also be done, with some work, directly in SQL. And I'm aware there are other open source date UDFs avaliable, e.g. iDate.

However...

When I wrote this I was not aware of any similar functions, and there may not have been any. (Though I probably got the idea from Scott Klement).
This is an efficient, light weight implementation in RPG.
Each function requires only 1 parameter and I like that convenience.
The code demonstrates creating an SQL UDF in RPG.

Functions

There are three functions,each taking a numeric field as input. Note: Newer versions of SQL will automatically cast character fields to numeric, otherwise you have to cast to numeric manually.

DATE_YMD to convert dates in either YYMMDD or CCYYMMD format.
- Example: 980129 or 19980129.
- Example: 210319 or 20210319
DATE_MDY to convert dates in MMDDYY or MMDDCCYY format.
- Example: 012998 or 01291998
- Example: 031921 or 03192021
DATE_CYMD to convert dates in CYYMMDD format.
- Example: 0990317
- Example: 1210317

Invalid Input

An invalid input value will return a null value and give a 01H99 SQLSTATE warning.

DATE_SQL

The RPG code for the DATE_SQL service program, which contains the functions.

DATE_SQLFX

This is DATE_SQL with the D-Specs in fixed form, as a convenience for any who might still be on older releases.

DATECRTFN

This is SQL "Create Function" code that tells SQL where the functions are. Use the RUNSQLSTM command or iACS Run SQL Scripts.

TEST_CYMD/TEST_MDY/TEST_YMD

There are example SQL statements that can be used to test the three functions.