Find Date Difference in AS400 Query

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
How are you calculating the date-difference now?
Dave FordSoftware Developer / Database AdministratorCommented:
Actually, what are the system data-types for DATEA and DATEB?
lcohanDatabase AnalystCommented:
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...
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Gary PattersonVP Technology / Senior Consultant Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
Icohan:  Query/400 doesn't use SQL.  No such thing as a CTE in this tool.
Dave FordSoftware Developer / Database AdministratorCommented:
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 AnalystCommented:
my bad....focused on DB2 on AS400...
Dave FordSoftware Developer / Database AdministratorCommented:
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 Commented:
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.
valmaticAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.