The requirement is to produce a YTD comparative report to last year YTD.

I need to end up with a where clause that resolves to this in sql:

where (b.inv_shrt_dt between RTRIM(CHAR(year(current date - 1 YEAR))) ||'-01-01' and current date - 1 year
or (b.inv_shrt_dt between RTRIM(CHAR(year(current date))) ||'-01-01' and current date))

I actually got it working with the define tool, to find out that this executes on every row, not the DB, so I was told to "do it with &var using -SET" and using the amper var in the where clause so that the where is executed on the server.

Any help would be greatly appreciated,
Frankie

Hope this helps:

-SET &DT = '20050510';
-SET &CURRENT_YR = 'NO';
-*-SET &CURRENT_YR = 'YES';

-SET &YR = EDIT(&DT,'9999');
-SET &YR = IF &CURRENT_YR EQ 'YES' THEN &YR ELSE IF &CURRENT_YR EQ 'NO' THEN &YR -1 ELSE &YR;
-TYPE The year used in WHERE statement is: &YR

-SET &WHERE_YR = 'where b.inv_shrt_dt between ' | &YR || '-01-01' ;
-TYPE &WHERE_YR

how do I SET &DT to "today's" date every day in YYMD format?

Thanks

Frankie:
Try this.
-SET &Y1 = &DATEYY;
-* Gets you the current year
-SET &Y2 = &Y1 - 1;
-* Gets you the prior year
-TYPE &|Y1 IS &Y1 &|Y2 IS &Y2
-EXIT

Thanks for the replies, they helped get me there! below is what I ended up with, I'm posting it so if anyone wants to give me "best practices" suggestions on the implementation, please do so! I had difficulty using DATEADD in a SET command, but it seemed to work fine in a DEFINE file ...??? That's why I parsed out THIS_MNTH_DAY and concatenated it to LAST_YR to get TO_DT_LY. I am also not certain if it was necessary for me to use DATECVT, but it's where i've ended up and I need to move on.

Here it is:
-SET &ECHO='ALL';
-SET &TODAY_YYMD=&YYMD;
-SET &THIS_YR=EDIT(&TODAY_YYMD, '9999');
-SET &THIS_MNTH_DAY=EDIT(&TODAY_YYMD, '$$$$9999');
-SET &LAST_YR=&THIS_YR - 1;
-SET &FROM_DT_LY=&LAST_YR||0101;
-SET &FROM_DT_LY_YYMD=DATECVT(&FROM_DT_LY,'I8','YYMD');
-SET &TO_DT_LY=&LAST_YR||&THIS_MNTH_DAY;
-SET &TO_DT_LY_YYMD=DATECVT(&TO_DT_LY,'I8','YYMD');
-SET &FROM_DT_TY=&THIS_YR||0101;
-SET &FROM_DT_TY_YYMD=DATECVT(&FROM_DT_TY,'I8','YYMD');
-RUN
JOIN
DIM_PERIOD.DIM_PERIOD.PERIOD_KEY IN DIM_PERIOD TO ALL
FCT_DLY_ITM_SLS.FCT_DLY_ITM_SLS.PERIOD_KEY IN FCT_DLY_ITM_SLS AS J0
END
TABLE FILE DIM_PERIOD
SUM
ITM_EXT_PRC_L_AMT
ACROSS
YR_NBR
WHERE TRANS_LVL_CD EQ 1
AND (((INV_SHRT_DT GE &FROM_DT_LY_YYMD) AND (INV_SHRT_DT LE &TO_DT_LY))
OR
((INV_SHRT_DT GE &FROM_DT_TY_YYMD) AND (INV_SHRT_DT LE &TODAY_YYMD)));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

Frankie,
Here's some stream-lined code that might work better. This may wrap strangely in the posting. The first -SET should be 2 lines. The other -SET statements are one line each.

-* Subtract 1 yr
-SET &LST_YTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),
- 'Y',-1)),'YYMD', 'I8YYMD');
-* Change month/day to Jan 1
-SET &CUR_BEG = EDIT(&DATEYY,'9999') | '0101';
-SET &LST_BEG = EDIT(&LST_YTD,'9999') | '0101';

You can't use the DATEADD function directly with amper-variables because the DATEADD function works on smart dates (which is why you can use it in a DEFINE) and amper-variables are not stored in date format. So to use DATEADD in the -SET for &LST_YTD you have to:
1) convert &YYMD to a date format using the DATECVT function
2) perform the DATEADD function to subtract 1 year
3) convert the smart date result back to a format that can be stored in an amper-variable -- I8YYMD here.

You can see this when you look at the code above by working through the parentheses from the inside out.

Your WHERE statement would look like this:

AND (((INV_SHRT_DT GE &LST_BEG) AND (INV_SHRT_DT LE &LST_YTD)) OR
((INV_SHRT_DT GE &CUR_BEG) AND (INV_SHRT_DT LE &YYMD)));

Hope this helps.

nice. thanks codermonkey.