|
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.
midrange.com
LSDTE is a BPCS numeric field that looks like CCYYMMDD
LSDTEA = DIGITS(LSDTE) creating an alpha field from the numeric BPCS 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
