We help IT Professionals succeed at work.

Find Date Difference in AS400 Query

valmatic asked
Hi.  I am comparing dates on a couple of different files and if those dates become out of balance by more than our 2 day buffer time, someone needs to react.  I was hoping to handle this with an IBM green screen query based report, which works for the most part but when the two dates split over a month end (April 30 and May 1) then the math gets skewed.  For example:  If DATEA = 115/05/01 and DATEB = 115/04/29 I would expect a 2 day difference but the result is actually 72.  If I create a field called NEWDATE that caculates [DATEA - 2]; the result is 1,150,499 instead of 1,150,429.   Screen Shot included  QryDate.pdf
I was wondering if there is still any way to calculate the difference between DATEA and DATEB in query?  
Dates are in CYYMMDD (115/04/23) format.

Watch Question

Dave FordSoftware Developer / Database Administrator

How are you calculating the date-difference now?
Dave FordSoftware Developer / Database Administrator

Actually, what are the system data-types for DATEA and DATEB?
lcohanDatabase Analyst

Here's an example for CHDLM format using a CTE table and DAYS() / CONAT() functions and all you need is to adjust to your CYYMMDD format in SUBSTR to get the exact values for your format:

WITH Converted (convertedDate) as (SELECT DATE(SUBSTR(chdlm, 1, 4) || '-' ||
                                               SUBSTR(chdlm, 5, 2) || '-' ||    
                                               SUBSTR(chdlm, 7, 2))
                                   FROM Chcart00
                                   WHERE chstat = '05')

FROM Converted

And a bit more reading about dati/time fun in DB2...

VP Technology / Senior Consultant
These are non-standard dates stored in a numeric field, not a proper DATE field.  So Query just sees them as numeric values, so it is doing exactly what you are asking it to do:

1150501 - 1150429 = 72.  

You'll need to convert them to proper DATE data type in order to do date math.  The format depends on your job date format.   Mine is currently *MDY (mm/dd/yy), so I'd do something like this:

Create result fields:

DATEA2  DATE(SUBSTR(DIGITS(DATEA+19000000),5,2))||'/'||SUBSTR(DIGITS(DATEA+19000000)),7,2)||'/'||SUBSTR(DATEB+19000000,3,2))
DATEB2 DATE(SUBSTR(DIGITS(DATEB+19000000),5,2))||'/'||SUBSTR(DIGITS(DATEB+19000000)),7,2)||'/'||SUBSTR(DATEB+19000000,3,2))

DIGITS add 19000000 to convert to a full 8-digit date.   Then DIGITS converts the numeric date to a character string.  Substrings then pulls out the year, month, and day portions of that string, and concatenation "||" is used to tack it all together into a date in character format (*MDY in my case) that the DATE function can then convert to a proper DATE field.  

You will need to rearrange it if you need a different sequence.  

Then you can do date math using DATEA2, and DATEB2.
Gary PattersonVP Technology / Senior Consultant

Icohan:  Query/400 doesn't use SQL.  No such thing as a CTE in this tool.
Dave FordSoftware Developer / Database Administrator

Gary and lcohan have effectively demonstrated why you should ALWAYS store a date-value in a DATE data-type... not numeric ... or character ... or anything else.

The minuscule advantage of the "space-savings" you get with those other data-types is FAR outweighed by how much harder it is to get "real" work done when you use them.

If you want a DATE, then always use a DATE data-type. You'll thank yourself later for doing so.

-- DaveSlash
lcohanDatabase Analyst

my bad....focused on DB2 on AS400...
Dave FordSoftware Developer / Database Administrator

Thanks, Gary. Like lcohan, I didn't pick up that valmatic is using Query/400. I just assumed he was using DB2 SQL ... since (IMHO) that's what any level-headed and talented database professional would be using in this century.

Bottom line, though, however it gets accomplished, valmatic will have to translate the data stored in the file to a real DATE in order to date arithmetic.

Gary PattersonVP Technology / Senior Consultant

I've got a few clients that still use query/400 extensively so it is still on my radar.  Time has definitely left it behind, and SQL offers so much more.  If I have to do green-screen reporting (something I avoid like the plague), I use Query Manager (QMQRY), which gives me some of the nice formatting stuff, level breaks, etc that you get with query/400, but with all the flexibility of SQL.

Personally, I prefer to deliver IBM i reports using Excel.
Just about total agreement with that last comment. Even when systems have Query/400 installed, I use QM. (I even create/use QM queries at times on systems that do not have the SQL Dev Kit installed.)

I'd make a minor change to the comment, though, in that "some of the nice formatting" doesn't go far enough, IMO.


Thanks Gary.  I apologize for the lateness on this.  I sort of found a workaround and was not getting notice on these after a spam filter switch so forgot all about it until I needed EE again..    Anyway,  I'm sure I'll run acrosss this again.  Beyond that, I've never used QM before but will investigate and I'm in the process of learning SQL as well so ...   thanks.