AS400 Define Results-Query...need Date help

[sueshe]

We are in version V05R03M00 IBM AS400. I am trying to build a query, and need to select records using Transaction Date (ISOTRDATE) of the first day of the current month to yesterday. (will run daily). I need to set this in Select Records so that the query can be run with no prompting for date changes. I am having a very hard time trying to figure out how to set up the result field for this calculation. I have been using
TODAY CURRENT(DATE)
TODAYISO CHAR(TODAY,ISO)
YESTERDAY TODAYISO - 1 DAYS

I don't know how to get the first day of the current month. Can someone help me, can't find any documentation on this that will help me, or that I understand. Thanks in advance for your help!!!

 

[Kozusnik]

sueshe,
Get YESTERDAY first so you don't miss the last day of the previous month.
YESTERDAY CHAR(CURRENT(DATE) - 1 DAY),ISO)

Then get the first day of that month.
FIRSTDAY SUBSTR(YESTERDAY,1,4)||'-'||
SUBSTR(YESTERDAY,6,2)||'-'||
'01'

Then in Select records
ISOTRDATE RANGE FIRSTDAY YESTERDAY

You can test it by replacing CURRENT(DATE) with DATE('12/01/07') or whatever date you want to look at.

Mark