|
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 **********************************************************************
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.