RE: Odd Question

["Dan Bale" <dbale@xxxxxxxxxxxxx>]

> -----Original Message-----
> From: mapics-l-bounces@xxxxxxxxxxxx / Shawn Thobe
> Sent: Friday, July 23, 2004 10:01 AM
>
> Does anybody no how to subtract dates in query.  When I do this it takes
> the numeric rep of the date and subtracts which works good unless the
> dates are in different months.  Thanks for any help.  I'm trying to use
> the DATE AND DAYS FUNCTIONS

TRNDT is a numeric cyymmdd date.  In "Define Result Fields":

Field       Expression                         Column Heading        Len
Dec
----------  ---------------------------------  --------------------  ---   -
--
TRANDTW4DY  TRNDT + 19000000                   Transaction             8
0
                                                  Date
                                               yyyy/mm/dd

TRANDT@     digits( TRANDTW4DY )

ZEROTIME    '-00.00.00.000000'

TRANDATETS  TimeStamp( substr(TRANDT@,1,4)||   Transaction
            '-'|| substr(TRANDT@,5,2) ||'-'||      Date
             substr(TRANDT@,7,2) || ZeroTime)   Timestamp

TRANDATE    Date( TRANDATETS )                 Transaction
                                                   Date

The reason I take it all the way to a timestamp, as opposed to just dropping
it into a date field, is that with a timestamp, you are guaranteed that the
date will be in yyyy-mm-dd format.  Otherwise, you are subject to whatever
the job's date format is set to.  TRANDATE is the date data type you can do
date arithmetic with and use the DAYS function.

hth,
db