Hi,

I have following global variable within edasprof.prf :

-SET &&LAST_M = DTADD( '&DATEYYMDm' , MONTH, -1);
-SET &&LAST_MD = DTADD('&DATEYYMDm', DAY, -1);
-SET &&THIS_MONTH_FIRST_DAY = EDIT(&YYMD,'9999$$$$') | EDIT(&YYMD,'$$$$99$$') | '01';

  

So what I expected for each of value, should be:

  
&&LAST_M = '2020/08/01' given YYMD type
&&LAST_MD = '2020/08/31' given YYMD type
&&THIS_MONTH_FIRST_DAY = '20200801' given A8

and as following with:

 
&&LAST_M = 43,678.00 (without given type)
&&LAST_MD = 43,708.00 (without given type)
&&THIS_MONTH_FIRST_DAY = 20,2009,901.00 (without given type) 

 

when I try using above global variable within SQL like:

 
SQL PREPARE SQLIN FROM 
SELECT 
   T1.product_id 
FROM 
   order_list T1 
 WHERE 
   T1.shipping_date < '&&LAST_M' 
END

 

and I get following error:

1  FILE(S) LOADED
(FOC14050) DATE IS EXPECTED ON LINE 27 AT ''43678''
(FOC009) Request failed validation, not executed.

  

I assuming &&LAST_M should be '2020/08/01' but as it appear it does not.
So I'm guessing within SQL the global variable is not given as Date type(where &&LAST_M = 43678) to compare as date format. Is there a way to SET global variable as Date format WITHOUT Define any field to specify format for &&LAST_M?(Or any other specific format)

This message has been edited. Last edited by: nox,

43678 is the number of days that 2020/08/01 is after the base date. You can convert this to a YYMD date with the DATECVT function.

-SET &&LAST_M = DATECVT( DTADD('&DATEYYMDm' , MONTH, -1), 'MDYY', 'A8YYMD');
-TYPE &&LAST_M 

If you need to have the slashes in the date:

-SET &&LAST_M = EDIT( DATECVT( DTADD('&DATEYYMDm' , MONTH, -1), 'MDYY', 'A8YYMD'), '9999/99/99');
-TYPE &&LAST_M 

Or you may find it simpler to use the AYM function:

-SET &&LAST_M = AYM(&DATEYY.EVAL&DATEM.EVAL, -1, 'I6YYM') ;
-SET &&LAST_M = EDIT(&&LAST_M, '9999/99/01') ;
-TYPE &&LAST_M 

Awesome, thank you dbeagan!