Search code examples
dateformatibm-midrange

IBM i (AS400/ISeries) - Adding days to date field in WRKQRY


I have a decimal date field (TDDATR) that is in the YYYYMMDD format.

I would like to create a field that is TDDATR + 30 days but I am unable to.

Using 'Define Results Field' I have tried a few things;

  1. Simply doing this;

TDDATR + 30 DAYS

But it returned this error: Labeled duration not used correctly.

  1. I tried using the DIGITS and SUBSTR commands to create a field in the DDMMYYYY format and then +30 days but got the same error.

  2. Same as above but in the DD/MM/YYYY format - same error.

  3. Using DATE(TDDATR) but all I see is +'s in the field.

  4. Using DATE( ) on the fields created in step 2 and 3 - still get +'s

I've ran out of ideas - any help would be greatly appreciated.


Solution

  • Based on the information here, I created the below 2 fields;

    TDDIGI  DIGITS(TDDATR)           
    
    TDDAT1  SUBSTR(TDDIGI,7,2)||'/'||
            SUBSTR(TDDIGI,5,2)||'/'||
            SUBSTR(TDDIGI,3,2)           
    

    From here I was able to create a date field;

    TDDAT2      DATE(TDDAT1)
    

    Which allowed me to perform the necessary calculations.

    The format of TDDAT1 is based on your job description which can be found by;

    • WRKJOB
    • Option 2
    • Page down
    • Date format..: X

    Mine was *DMY, so TDDAT1 was formatted based on this.