RE: What would be the easiest way to do this

Aaron,

Something like this perhaps;


SELECT *
  FROM filename
  WHERE MONTH(PrevMonthTempDate) = MONTH(Snd_Date)
  AND   YEAR(TempDate)           = YEAR(Snd_Date)

John B.


-----Original Message-----
From: Bartell, Aaron L. (TC) [mailto:ALBartell@taylorcorp.com]
Sent: Wednesday, March 27, 2002 1:02 PM
To: 'rpg400-l@midrange.com'
Subject: What would be the easiest way to do this


Here's the deal.  I am creating a report with embedded SQL in RPG that runs
on the first day of every month.  I need it to get data based on a date
field from the 1st day of the previous month to the last day of the previous
month.  For example: If I ran on 4/01/2002 I would need the date range to be
03/01/2002 through 03/31/2002.

What is the best way to do this in code.  I am mostly asking because I am
wondering if some of the new BIF's could help me out because this just looks
ugly to me.

Here is what I am doing right now:
     D TempDate        S               D
     D TempDate2       S              8  0 inz
     D CharDate        S              8    inz
     D TempTime        S               T   inz
     D TempTime2       S              6  0 inz
     D SQLWhere        S           1024    inz

     C                   move      *DATE         TempDate
     C     TempDate      SUBDUR    1:*M          TempDate
     C                   move      TempDate      TempDate2
     C                   move      TempDate2     CharDate
     C                   eval      %subst(CharDate:7:2) = '01'
     C                   eval      SQLWhere = %trim(SQLWhere) +
     C                             ' (SND_DATE >=' + CharDate + ') AND' +

     C                   move      *DATE         TempDate2
     C                   move      TempDate2     CharDate
     C                   eval      %subst(CharDate:7:2) = '01'
     C                   eval      SQLWhere = %trim(SQLWhere) +
     C                                       ' (SND_DATE <' + CharDate +  +
')'

Result Where clause:
WHERE (SND_DATE >= 20020201) AND (SND_DATE < 20020301)

There has got to be a better way to do this.

Aaron Bartell
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


End of RPG400-L Digest