RE: Odd Question

["Shawn Thobe" <ShawnT@xxxxxxxxxxxxxxxxxxxxxxxxxx>]

THANKS

Shawn Thobe
Supervisor - Information Systems
Fort Recovery Industries Inc.
419-375-4121
 

-----Original Message-----
From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx] 
Sent: Friday, July 23, 2004 10:23 AM
To: MAPICS ERP System Discussion
Subject: RE: Odd Question

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

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