Will like to run my report weekly using date variables to calculate the current month (Current Year) and previous month (Previous Year). This will be an automated report from Report Caster.

My Code so far.

-SET &FROM_DATE = EDIT((DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),M,0)),'YYMD','I8YYMD')), '999999$$') || '01';
-TYPE &FROM_DATE
-SET &DAYS = EDIT(&YYMD, '$$$$$$99');
-SET &END_DATE = (DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),D,-&DAYS)),'YYMD','I8YYMD'));

-SET &FROM_DATE_LY = EDIT((DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),M,-1)),'YYMD','I8YYMD')), '999999$$') || '01';
-SET &END_DATE_LY = (DATECVT((DATEADD((DATECVT(&FROM_DATE_LY,'I8YYMD','YYMD')),D,-&DAYS)),'YYMD','I8YYMD'));

Any Help will be much appreciated.

This message has been edited. Last edited by: <Kathryn Henning>,

Can you provide an example of the fours dates?

Here is how i will like to use the dates.

WHERE ( FCT_SALE.FCT_SALE.INVOICE_UPDATE GE '&FROM_DATE' ) OR ( FCT_SALE.FCT_SALE.INVOICE_UPDATE GE '&FROM_DATE_LY' );

so, FROM_DATE (20131001) AND END_DATE (20131031)
FROM_DATE (20121001) AND END)DATE (20121031)

Thank you again.

Hi,

Run this bit of code and see if this will help.

-*
-* SET CURRENT DATE
-SET &CUR_YYMD = &YYMD;
-*
-* SET CURRENT MONTH CURRENT YEAR DATES
-SET &BCMCY_YYMD = DATECVT(DATEMOV(DATECVT(&CUR_YYMD, 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD');
-SET &ECMCY_YYMD = DATECVT(DATEMOV(DATECVT(&CUR_YYMD, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');
-* SET PREVIOUS MONTH CURRENT YEAR DATES
-SET &EPMCY_YYMD = DATECVT((DATEADD((DATECVT (&BCMCY_YYMD, 'I8YYMD', 'YYMD')),'D', -1)), 'YYMD','I8YYMD');
-SET &BPMCY_YYMD = DATECVT(DATEMOV(DATECVT(&EPMCY_YYMD, 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD');
-* SET CURRENT MONTH PREVIOUS YEAR DATES
-SET &BCMPY_YYMD = DATECVT((DATEADD((DATECVT (&BCMCY_YYMD, 'I8YYMD', 'YYMD')),'Y', -1)), 'YYMD','I8YYMD');
-SET &ECMPY_YYMD = DATECVT(DATEMOV(DATECVT(&BCMPY_YYMD, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');
-* SET PREVIOUS MONTH PREVIOUS YEAR DATES
-SET &BPMPY_YYMD = DATECVT((DATEADD((DATECVT (&BPMCY_YYMD, 'I8YYMD', 'YYMD')),'Y', -1)), 'YYMD','I8YYMD');
-SET &EPMPY_YYMD = DATECVT(DATEMOV(DATECVT(&BPMPY_YYMD, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');
-*
-TYPE --------------------------------------------------------------
-TYPE CURRENT DATE (CUR_YYMD) ------- &CUR_YYMD
-TYPE --------------------------------------------------------------
-TYPE --- CURRENT MONTH CURRENT YEAR DATES
-TYPE --------------------------------------------------------------
-TYPE BEGIN CURRENT MONTH CURRENT YEAR (BCMCY_YYMD) ----- &BCMCY_YYMD
-TYPE END CURRENT MONTH CURRENT YEAR (ECMCY_YYMD) ----- &ECMCY_YYMD
-TYPE --------------------------------------------------------------
-TYPE --- PREVIOUS MONTH CURRENT YEAR DATES
-TYPE --------------------------------------------------------------
-TYPE BEGIN PREVIOUS MONTH CURRENT YEAR (BPMCY_YYMD) ----- &BPMCY_YYMD
-TYPE END PREVIOUS MONTH CURRENT YEAR (EPMCY_YYMD) ----- &EPMCY_YYMD
-TYPE --------------------------------------------------------------
-TYPE --- CURRENT MONTH PREVIOUS YEAR DATES
-TYPE --------------------------------------------------------------
-TYPE BEGIN CURRENT MONTH PREVIOUS YEAR (BCMPY_YYMD) ----- &BCMPY_YYMD
-TYPE END CURRENT MONTH PREVIOUS YEAR (ECMPY_YYMD) ----- &ECMPY_YYMD
-TYPE --------------------------------------------------------------
-TYPE --- PREVIOUS MONTH PREVIOUS YEAR DATES
-TYPE --------------------------------------------------------------
-TYPE BEGIN PREVIOUS MONTH PREVIOUS YEAR (BPMPY_YYMD) ----- &BPMPY_YYMD
-TYPE END PREVIOUS MONTH PREVIOUS YEAR (EPMPY_YYMD) ----- &EPMPY_YYMD
-TYPE --------------------------------------------------------------

So From Date is always the first of the month and End Date is always the end of the month?

-SET &FROM_DATE =
-  DATECVT( DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOM'), 'YYMD','I8YYMD');

-SET &END_DATE =
-  DATECVT( DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'EOM'), 'YYMD','I8YYMD');

-SET &FROM_DATE_LY = 
-  DATECVT( DATEADD(DATECVT(&FROM_DATE,'I8YYMD','YYMD'),'Y', -1), 'YYMD','I8YYMD');

-SET &END_DATE_LY = 
-  DATECVT( DATEADD(DATECVT(&END_DATE,'I8YYMD','YYMD'),'Y', -1), 'YYMD','I8YYMD');

-? &FROM
-? &END

jfr99, you code worked, thank you again both of you for your help.