Validate a Legacy iSeries Date Using SQL

Posted on by

SQL is very useful for data hygiene projects allowing analysts to check the validity and integrity of the database. However, checking that a legacy date is correct cannot be done without the use of a user-defined function.

The following function accepts a numeric date in the format ddmmyy. This is converted into standard date format and validated. The function returns a Y/N flag to indicated whether or not the date is valid.

Create Function QGPL/VALDMY 
(DateDec Decimal (6, 0) ) 
Returns Char(1) 
Language SQL 
Deterministic 
Contains SQL 
Not Fenced 
Returns Null On Null Input 
No External Action 
Set Option Commit=*None, UsrPrf=*Owner 
Begin 
   Declare charDate Char(10); 
   Declare testDate Date; 
   Declare Exit Handler For SQLException 
   Return 'N'; 
   Case 
   When Substr(Digits(DateDec), 5, 2) < '50' Then 
      Set charDate = 
      '20' 
      ConCat Substr(digits(DateDec), 5, 2) 
      ConCat '-' 
      ConCat Substr(digits(DateDec), 3, 2) 
      ConCat '-' 
      ConCat Substr(digits(DateDec), 1, 2) 
      ; 
   When Substr(Digits(DateDec), 5, 2) >= '50' Then 
      Set charDate = 
      '19' 
      ConCat Substr(digits(DateDec), 5, 2) 
      ConCat '-' 
      ConCat Substr(digits(DateDec), 3, 2) 
      ConCat '-' 
      ConCat Substr(digits(DateDec), 1, 2) 
      ; 
 End Case; 
 If charDate Is Null Then 
    Return 'N'; 
 End If; 
 Set testDate =Cast(charDate As Date); 
    Return 'Y'; 
End

Use this command to create the function:

RUNSQLSTM SRCFILE(QGPL/QSQLSRC) SRCMBR(VALDMY)

The function can be used as follows to find invalid dates:

SELECT custnr, orddat, valdmy(orddat)
FROM   orders
WHERE  valdmy(orddat) = 'N'



Customer  Nr Order Date  VALDMY ( ORDDAT )
 000001          182215         N
 000009          116215         N
******** End of data ********

Using iSeries SQL Functions for Legacy Date Handling

Many iSeries applications still have dates which are stored as numeric fields in the database. This can be problematic when using SQL to process the data particularly when the dates are stored in formats such as ddmmyy. It is well worth tapping into the flexibility and re-usability offered by SQL functions.

Consider a simple example showing the customer number & order date:

SELECT custnr, orddat 
FROM   orders
Customer Nr     Order Date
     000001         180215
     000002         251214
     000004         170115
     000009         110215
********  End of data  ********

It is not possible to make meaningful date range selections using the order date in its current ddmmyy format.  However, by creating a simple SQL function, we can convert this legacy date into a “real” date which will allow tasks such as selection.

Here is how our new function cnvdmy would be used in an SQL statement.

SELECT custnr, orddat, cnvdmy(orddat)
FROM   orders
WHERE  cnvdmy(orddat) > '2015-02-01' 
 
Customer Nr  Order Date  CNVDMY ( ORDDAT )
     000001      180215         2015-02-18
     000009      110215         2015-02-11
********  End of data  ********

The source below can be used to create this function. You can either use PDM (or iSeries Navigator) depending on your preference.

Note that the year 1950 is used in the When statement for the date window value. You may need to change this value depending on your application or context.

Create Function QGPL/CNVDMY 
(DateDec Decimal (6, 0) ) 
  Returns Date 
Language SQL 
Deterministic 
Contains SQL 
Returns Null On Null Input 
No External Action 
Begin 
  Declare ReturnDate Date ; 
  Declare Exit Handler for SQLState '22007' 
  Return Cast(Null As Date); 
  Case 
     When Substr(Digits(DateDec), 5, 2) < '50' Then
        Set ReturnDate = 
        Date( '20' 
        ConCat Substr(digits(DateDec), 5, 2) 
        ConCat '-' 
        ConCat Substr(digits(DateDec), 3, 2) 
        ConCat '-' 
        ConCat Substr(digits(DateDec), 1, 2) 
     ); 
     When Substr(Digits(DateDec), 5, 2) >= '50' Then 
        Set ReturnDate = 
        Date( '19' 
        ConCat Substr(digits(DateDec), 5, 2) 
        ConCat '-' 
        ConCat Substr(digits(DateDec), 3, 2) 
        ConCat '-' 
        ConCat Substr(digits(DateDec), 1, 2) 
      ); 
   End Case; 
 Return ReturnDate; 
End

Run the following command to create the function:

RUNSQLSTM SRCFILE(QGPL/QSQLSRC) SRCMBR(CNVDMY)  
Posted in SQL.