Query and Date Durations

Hey, Ted:

We have a physical file that contains two dates. Both are stored as eight-digit zoned-decimal numbers in YYYYMMDD format. We recently wrote a query to find the number of days between the two dates. Everything was going fine, until one morning a user called us to ask why there were 102 days between October 2 and November 4. We're wondering the same thing.

Here are the result fields we defined in the query:

Name        Expression               
______________________________________
ENTDTD      digits(entdt)            
ENTDTX      substr(entdtd,5,2)||'/'||
            substr(entdtd,7,2)||'/'||
            substr(entdtd,3,2)       
ENTDATE     date(entdtx)             
REQDTD      digits(reqdt)            
REQDTX      substr(reqdtd,5,2)||'/'||
            substr(reqdtd,7,2)||'/'||
            substr(reqdtd,3,2)       
REQDATE     date(reqdtx)             
DIFF        reqdate - entdate        

--Helen

When you subtract one date from another, Query gives you an eight-digit date duration. The first four digits represent a number of years, the next two digits are a number of months, and the last four digits represent a number of days. The value 102 means one month and two days, not 102 days. Here is the correct way to determine the number of days between the two dates.

Name        Expression
_________________________________________
DIFF        days(reqdate) - days(entdate)

The days function returns an ordinal value for a date. The difference between these two ordinal values is what you need.

However, when adding or subtracting an elapsed time, you can't use a duration. For example, if you want to add one month and two days to a date, use this syntax:

Name        Expression
______________________________________
DUEDATE     entdate + 1 month + 2 days

--Ted

Sponsored By INFORM DECISIONS

ELIMINATE THE COSTS OF PRE-PRINTED FORMS, LABOR AND POSTAGE WITH inFORM Decisions iDocs™ 'Suite' iSeries based e-Forms, e-Checks, e-Mail, e-FAX, and Document Retrieval from the Web are available as individual modules or as a complete e-Document processing 'Suite'. Click to Download the Complete Suite or Individual Modules today www.inFORMDecisions.com or call (800) 858-5544

---

Editors: Howard Arner, Joe Hertvik, Ted Holt, David Morris,

Managing Editor: Shannon Pastore

Publisher and Advertising Director: Jenny Thomas

Advertising Sales Representative: Kim Reed

Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

---