Re: QRY and Dates -- MIDRANGE-L

Oy! I assumed SQL when the poster said query. Oh, well, the sametechniques pertain, except for result fields, which don't exist inSQL, and whose expressions need to be written out everywhere they are used.


It's been SOOOOOOO long since I used Query/400!  ;-)

At 06:59 PM 1/30/2006, you wrote:

I got the following info from a BPCS_L  discussion some time ago.
* We can only do sub-stringing and concatenation of data in query/400 when
that data is in the format of alpha field.  We can get numeric there using
DIGITS conversion.
* With the aid of sub-stringing and concatenation, we can get a field into
IBM date math format, then from that point we can get # days between
current date of running the report to select data that is a few days ago,
or a few days into the future, or like a range ... from 10 days ago to 10
days from now.

    LSDTE is a BPCS numeric field that looks like CCYYMMDD

LSDTEA = DIGITS(LSDTE) creating an alpha field from the numericBPCS field

    LSDTEAA = SUBSTR(LSDTEA,5,2)||'/'||
              SUBSTR(LSDTEA.7,2)||'/'||
              SUBSTR(LSDTEA,3,2)
              This is now an alpha field that looks like MM/DD/YY
    LSDTED = DATE(LSDTEAA)
              This is now a true ISO DATE TYPE for IBM DATE MATH
              even though it has the // alpha embedded characters
              which is a nuance of capability Al was unaware of
              until seeing the BPCS_L discussion of Query/400.

Warning, in BPCS, and possibly other software, the Date field sometimes
contains data that is not a valid date, such as 99/99/99 and
00/00/00.  This is not an error, but part of how the BPCS software
functions.  Having these values in the date field are to tell the BPCS
software specific information about the records that contain those values,
such as "expired", "inactive", "perfected".

When you are running some software that ASSUMES only valid dates should be
there, you need to make some provision for do what, if there is data there
that does not translate into a valid date.  In Query/400 it grabs all the
data, does the math, then decides what records to select, so if your
selection criteria is based on calculations of a field that can contain
invalid data, your Query/400 can bomb.

It also helps if you know which date fields get this 99/99/99 etc.
treatment, and which do not.

    LSDTEDYS = DAYS(LSDTED)
               This gives us days since a distant check point for that date
(a standard) ... we can then compare this to same calculation against some
other date, and get difference in # of days.

   Having done this with several date fields
   DAYS_DIFF = DAYS(DT_NEW) - DAYS(DT_OLD)
               such that a positive # is days into the future
                     and a negative # is days into the past

You can also do this with MONTH or YEAR math.

  YEAR_AGO = CURRENT(DATE) - 1 YEAR

You can extract YEAR MONTH DAY #s but this is not advisable due to 28 days
in Feb etc. can't do exact days math that way.

  TODAY = CURRENT(DATE)                 Today in mm/dd/yy format

  TOMORROW = DAYS(CURRENT(DATE)) + 1
             This gives us days count equivalent for day after today
  Selection criteria
  LSDTEDYS EQ TOMORROW

  YESTERDAY = DAYS(CURRENT(DATE)) - 1
             This gives us days equivalent for day before today
  Selection criteria
  LSDTEDYS EQ YESTERDAY

  Once you have the CCYYMMDD date converted from numeric, through digits to
alpha, so you can sub-string it, then convert to mm/dd/yy date math field,
you can do date math to get some # days between that and CURRENT, which is
the date you are running the Query/400.    I do this with a lot of reports
to get at data that is in the last 7 days, last 10 days, due in the next 2
weeks, and so forth.

>Hi, I've got a file containing dates in yyyymmdd format.  The date
>contains entries that are about a year old.  I want to use query, if
>possible, to report on records where the dates are the current date or the
>current date -1 (yesterday).
>
>Does anyone have any examples of how this can be done?
>
>Any help appreciated.
>
>Thanks
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.