Query Date Math

See Query for links to more tips.

Introduction

Many Query users want to do date math, where we find out how many days ago some date was, or we select data based on including stuff for the last 7 days, and we not want to force the user to always have to key in whatever cut-off date to use. We would rather key in how many days to include, and let Query do the date math.

Restrictions

There are certain kinds of Query operations that can only be done on certain Query Field Types so it is often neccessary to convert data between field types to get the desired actions.

Query can only do date math on fields that are in the ISO Date data format. Many software packages store dates in some other format, so before doing Date Math on the contents of those fields, they need to be converted to IBM 400 ISO Date data format. This article will include example(s) of how to do that.

We cannot do IBM Date Math on the contents of a data field whose contents are numeric data format, so we need to convert that data to a format that works.

Rearranging Day Month Year etc.

Rearranging requires Query Substrings and Query Concatenation which can only be done on Query Field Types Alphanumeric, so:

• the numeric date field needs to be converted from numeric to alpha;
• then do the substringing and concatnation;
• then when the alpha field has the day month year in the right sequencing and characters of the data to do the date math,
• this alpha mixture needs to be converted from alpha to date format.
• Now we can do Date Math on the end results of this convoluted Date Manipulation.

BPCS LRDTE Example

LRDTE is a BPCS numeric field in file ECL of version 405 CD that looks like CCYYMMDD. It means the due date to ship some item on a customer order line.

In the results area of WRKQRY for some query, define the following fields in the sequence given ... in other words, a field must be defined before it is used in a later field.

 LRDTEA = DIGITS(LRDTE)

• LRDTEA is an alpha field containing a copy of LRDTE contents
• DIGITS is the Query operation that we use to convert a numeric field into an alpha field.

LRDTEAA = SUBSTR(LRDTEA,5,2)||'/'||
          SUBSTR(LRDTEA,7,2)||'/'||
          SUBSTR(LRDTEA,3,2)

• SUBSTR(LRDTEA,5,2) extracted the 2 characters starting in position 5 of CCYYMMDD which are MM, the month.
• SUBSTR(LRDTEA,7,2) extracted the 2 characters starting in position 7 of CCYYMMDD which are DD, the day.
• SUBSTR(LRDTEA,3,2) extracted the 2 characters starting in position 3 of CCYYMMDD which are YY, the year.
• each || double vertical line concatenates something together such as '/' which is part of what belongs inside an ISO date format.
• We have now converted the LRDTE numeric data from CCYYMMDD to MM/DD/YY but it is still an alpha result field.

 LRDTED = DATE(LRDTEAA)

• This is now a true ISO DATE TYPE for IBM DATE MATH. Note that it has the '/' alpha embedded characters.
• DATE

  LRDTEDYS = DAYS(LRDTED}

• This gives us days since a distant check point vs. LRDTE date.
• DAYS

  TODAY = CURRENT(DATE)
  TOMORDAY = DAYS(CURRENT(DATE}} + 1