Re: Odd Question

[Ron <Ron_H@xxxxxxxxxx>]

In order to do date arithmetic for MAPICS dates in Query/400 you need to 
do three things:
1) Convert the date to character
2) Change the date to the job format (probably MM/DD/YY for most systems 
in the U.S.) in character format
3) Convert the character string to a DATE field and use the DAYS function 
to return a numeric

Here's an example I used for a query of the POMAST file:

Field           Expression 
ACTDTC          DIGITS(actdt) 
(Convert MAPICS date column to character) 
  
 
ACTDTN          SUBSTR(actdtc,4,2) || '/' || 
                        SUBSTR(actdtc,6,2) || '/' || 
                        SUBSTR(actdtc,2,2) 
(Convert MAPICS date to MM/DD/YY format)

DATEFLD3        DAYS(DATE(ACTDTN)) - 
                        DAYS(DATE(MDATEN)) 
(Convert the field to a DATE field and then use the DAYS function in the 
calculation)

You'd be surprised at the date functions you can get Query/400 to perform.
Check out some of these links for the some of the date capabilities of 
Query/400. 
http://www.midrangeserver.com/fhg/fhg052604-story03.html
http://www.midrangeserver.com/mgo/mgo092403-story01.html
http://www.mcpressonline.com/mc/.6ae686b0
http://www.midrangeserver.com/mgo/mgo100303-story01.html
http://www.midrangeserver.com/mgo/mgo120503-story02.html
http://www.midrangeserver.com/mgo/mgo090303-story01.html
http://www.midrangeserver.com/mgo/mgo102403-story02.html


Ron Hankey - CPIM
iSeries Administrator and Senior Applications Programmer


Dan wrote:
>Does anybody no how to subtract dates in query.
<SNIP>