RE: [SYSTEM21] Query Date Program

["Brunk, Kevin" <KBrunk@xxxxxxxxxxxx>]

The following is from an online article entitled "Looking for a good date for 
Query/400?".  I don't remember the source, but I think it's techniques will 
lead you to your answer.  You'll simply need to convert your dates using the 
method described and them subtract the DAYS function between them 
DAYS(DATE(field1) - DATE(field2)).   [I've not tried this so what I'm claiming 
may not turn out to be true!]

==Kevin 

Malcolm Westley 
07 Sep 2001, Rating 3.60 (out of 5)

Query/400 has many useful date functions. Unfortunately, they do not work with 
the 7 or 8 digit numeric fields often used to store dates in files. Luckily, 
Query/400 also includes two functions that can be used to transform these 
reluctant dates into something Query/400 will dance with. 

First, define an intermediate result field (called DT in this example) using 
the DIGITS function on the numeric date field: DIGITS(7 or 8 digit numeric date 
field)/  DT will be a character representation of the numeric date field. 

Next, use the SUBSTR function to change the intermediate field into the job 
date format (in this example MDY from a 7 digit number CYYMMDD): 

 FIELD    EXPRESSION
DATEMDY  SUBSTR(DT,4,2) || '/' || 
         SUBSTR(DT,6,2) || '/' ||
         SUBSTR(DT,2,2) 
The field DATEMDY will now be correctly interpreted by Query/400 as a date 
field.  Therefore, you can use the date-specific functions like YEAR, DAY, 
MONTH, DAYS on this field.  

If DATEMDY contained someone's hire date, the following expression will return 
the number of years worked: 

YEAR(CURRENT(DATE)-DATE(DATEMDY))

-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx
[mailto:system21-bounces@xxxxxxxxxxxx]On Behalf Of Mex Davis
Sent: Thursday, August 05, 2004 2:40 PM
To: system21@xxxxxxxxxxxx
Subject: [SYSTEM21] Query Date Program


As anyone been successful using Query/400 to find the number of days between
two dates?
We hear that there is a way but for the life of us, we don't see how you
can.
If you got any hints, we'd like your help.
Thanks.
Mex Davis
IS Manager
Bekum America Corp.


_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.



**********************************************************************
CONFIDENTIALITY NOTICE:  The information transmitted in this message is 
intended only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material.  Any review, retransmission, 
dissemination or other use of this information by persons or entities other 
than the intended recipient is prohibited.  If you received this in error, 
please contact the sender and destroy all copies of this document.  Thank you.  
The Butler Company
**********************************************************************