CODEWITHSUNDEEP
dateformatibm-midrange
user5085719
user5085719

Reputation:

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.

Upvotes: 0

Views: 6146

Answers (4)

David Chizek
David Chizek

Reputation: 1

IBM Web Query is no longer an option as it has been discontinued as of 10/10/23

Upvotes: 0

user5085719
user5085719

Reputation:

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.

Upvotes: 0

Jairo R. Flores
Jairo R. Flores

Reputation: 734

The use of days is as follow

Field       Expression                       
CURDATE_30  days(current(date)) + 30

The solution to your problem is: given the field A dec(8,0)

Field       Expression                       
YYYYMMDD_   date(substr(digits(a),5,2)||'/'||
             substr(digits(a),7,2)||'/'||
             substr(digits(a),3,2))      

NEXT_MONTH  DAYS(YYYYMMDD_) + 30

Remember to check the date format in your job description. In the example the format is MDY or MM/DD/YY.

More info here

Upvotes: 0

Charles
Charles

Reputation: 23783

Query/400 lacks a lot of the features that an SQL based interface has.

I'd urge you to consider switching to Query Manager (STRQM) which is a fully SQL based product. You can even convert Query/400 queries to Query Manager queries with the RTVQMQRY command by having the ALWQRYDFN parm set to *YES.

The other option that IBM is pushing is Web Query. Again, fully SQL based and you can convert Query/400 queries into it.

Having said that, the problem is that FLD + 30 DAYS only works when FLD is a DATE data type. Query/400 includes a DATE() function to convert non-date types into date. But it's very limited in that it only works with character fields formatted according to your job defaults. Assuming you're in the US, it'd only work with a character value of '07/01/15'.

You could do a lot of manipulation in Query/400 and end up with a result field that meets DATE()'s requirements. But a better solution would be to create an SQL view over your table and have your numeric date converted into a date data type in the view.

You can find code examples that show how to convert a numeric YYYYMMDD to a actual date data type in the view. However, I'd recommend create a user defined function (UDF) that will do the conversion for you. That will make it much easier to use in the view and to reuse in other places.

If you'd like, there's an open source package called iDate, that includes all the code required for convert to/from date data types.

Download that, install/compile it and your SQL view becomes

select ... idate(TDDATR,'*CCYMD') as TD_DATE
from myfile

Upvotes: 2

Related Questions