Comparing DB2 DATES - IBM i dates not compatible with DB2 dates SQL

I'm trying to create a report shows orders that are late, due current month, next month, etc. pulling data from IBM i server (AS/400) using DB2 SQL. However, I'm running into issues with comparison operators. I have a potential solution, but it is so tedious that it would make my code significantly longer and very prone to errors.

For example, I am pulling customer orders by Request Date:

  SELECT C6D0NB /* REQUEST DATE */                   
    FROM MBC6REP /* CUSTOMER ORDER FILE */             
   WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */   
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

which results in:

 Request 
 date    
118/08/28 
118/06/29 
118/06/15 
118/06/07 
....
117/07/21 
117/06/14 
117/04/14 
117/03/01

So, this "Request Date" (C6D0NB) field is labeled as a "date" format within my ERP system (Infor XA). I can use comparison operators to use static comparisons (e.g. between January 1, 2018 and January 15, 2018, inclusive):

   SELECT C6D0NB /* REQUEST DATE */                     
     FROM MBC6REP /* CUSTOMER ORDER FILE */               
    WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND 
          C6D0NB >= '1180101' AND                              
          C6D0NB <= '1180115'                                  
 ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

with results:

  Request 
  date    
 118/01/15 
 118/01/15 
 118/01/12 
 118/01/12 
 ....
 118/01/03 
 118/01/03 
 118/01/03 
 118/01/02

However, when I want to use the "CURRENT DATE" function, I run into errors. When I query:

SELECT CURRENT DATE   
FROM SYSIBM.SYSDUMMY1

I receive the expected:

CURRENT DATE
02/12/18

I can use the DAY, MONTH, YEAR functions as expected:

SELECT DAY(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1

Which returns:

DAY 
12

I cannot however, combine the two:

  SELECT C6D0NB /* REQUEST DATE */                    
    FROM MBC6REP /* CUSTOMER ORDER FILE */              
   WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
         C6D0NB >= CURRENT DATE                              
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

which gives me the error:

 Comparison operator >= operands not compatible.

If I try using MONTH with "Request Date",

  SELECT MONTH(C6D0NB) /* REQUEST DATE */           
    FROM MBC6REP /* CUSTOMER ORDER FILE */            
   WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */  
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

I receive this error:

Argument 1 of function MONTH not valid.

How do I create a dynamic report to query, for example, all dates within current month? I can run the following code, but it's extremely tedious:

    SELECT C6D0NB /* REQUEST DATE */              
    FROM MBC6REP /* CUSTOMER ORDER FILE */               
    WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
    MONTH(TO_DATE((CONCAT(
             CONCAT(
             (CONCAT(SUBSTRING(C6D0NB,4,2), '/')),
             (CONCAT(SUBSTRING(C6D0NB,6,2), '/'))),
             SUBSTRING(C6D0NB,2,2))), 'MM/DD/YY')) = MONTH(CURRENT DATE) AND
    YEAR(TO_DATE((CONCAT(
             CONCAT(
             (CONCAT(SUBSTRING(C6D0NB,4,2), '/')),
             (CONCAT(SUBSTRING(C6D0NB,6,2), '/'))),
             SUBSTRING(C6D0NB,2,2))), 'MM/DD/YY')) = YEAR(CURRENT DATE)  
    ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

Is this really the most concise way to code it?

Thanks!

Solution

C6D0NB is not actually a Date column, it's a numeric column whose values is interpreted by your ERP as a date in CYYMMDD format.

The IBM i and it's prior incarnations the iSeries and AS/400 have supported true date types for a long time.

Unfortunately, they are not used in many older applications...

Best solution, use a UDF to convert from the numeric value to an actual date. You could write your own, or download Alan Campin's iDate tools...

Then you'll be able to simply do...

  SELECT C6D0NB /* REQUEST DATE */                    
    FROM MBC6REP /* CUSTOMER ORDER FILE */              
   WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
         idate(C6D0NB,'*CYMD') >= CURRENT DATE                              
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

Actually, from a performance perspective, it'd be better to convert the date to a CYYMMDD numeric. Alan's package includes such a function...

  SELECT C6D0NB /* REQUEST DATE */                    
    FROM MBC6REP /* CUSTOMER ORDER FILE */              
   WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
         C6D0NB >= convertToiDate(CURRENT DATE, '*CYMD')                              
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */

This allows indexes on C6D0NB to be used...

Alternately, you can create a "Dates" or "Calendar" Table. besides using it to convert from numeric dates to actual date types...it's useful for lots of other set based operations...in any DB.