Query Date Math

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.

Also see Date Math Gotcha.

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 is the query operation that we use to convert an alphabetical field into a date field.

  LRDTEDYS = DAYS(LRDTED}

• This gives us days since a distant check point vs. LRDTE date.
• We can do math comparing 2 dates converted to corresponding # days from the same point.
• DAYS converts a date field into some number of days

Current Date

  TODAY = CURRENT(DATE)
  TOMORROW = TODAY + 1 DAY
  YESTERDAY = TODAY - 1 DAY
  WEEK_AGO = TODAY - 7 DAYS
  YEAR_AGO = TODAY - 1 YEAR

• CURRENT(DATE) extracts into ISO date format whatever is the current date that this query is being run.
• TOMORROW, in this example, is the next date.
• YESTERDAY, in this example, is the previous day.
• By this means, we can get at some date based on how many days from TODAY, then compare result of that to date in our data base, provided that date has been converted to the ISO date format, as illustrated above

Days Difference

Let's suppose we have defined 2 dates into ISO date format.

• DT_NEW is what should be a contemporary date where some action is needed
• DT_OLD is some date where some action happened

  DAYS_DIFF = DAYS(DT_NEW) - DAYS(DT_OLD)

• a positive # result means # days into the future for DT_NEW ahead of DT_OLD
• a negative # result means # days into the past for DT_NEW prior to DT_OLD

  DAYS_AGO = DAYS(TODAY) - DAYS(SHIP_DATE)
  DAYS_DUE = DAYS(TODAY) - DAYS(DUE_DATE) 

• These examples assume we have located
  • date in our application where something was shipped
    • and converted it to ISO date format, and now we can get how many days ago that shipment was, relative to the current date when the Query was run
  • date in our application where something is due to be shipped out
    • and converted it to ISO date format, and now we can get how many days from now that comes to

Given this Query date math, the selection criteria can call to include data where the

• DAYS_AGO of shipments was within the last 10 days, 3 days, 30 days, etc.
• DAYS_DUE is in the next 14 days
• at Query run time, the user can change # days desired on the selection criteria