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.

Rearranging Day Month Year etc.

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 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.