March 06, 2006, 12:07 PM
BethanyDates, date functions and where statements
My report needs the user to submit a date and then the report needs to give data based on the date submitted by the user as well as the same date last year. Getting data with the date submitted by the user isn't a problem, but I'm trying to use the DATEADD function to get the data for the same date last year and get the following error message:
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
Here is the code I'm using (&CY_DATE is the user inputed date field in format MDYY):
PRINT
LINE_AMT
BY LINE_SA_ID_TYPE
BY LINE_COMMON_ID AS LY_EMPLID
BY ITEM_ACCOUNT_TERM
WHERE LINE_POSTED_DATE LE DATEADD(&CY_DATE, 'Y', -1);
ON TABLE PCHOLD FORMAT EXL2K
END
Any help is appreciated.
March 06, 2006, 12:33 PM
LeahYou need to convert the input date to a smart date that you can subtract from. The entered date is considered 'alpha' by the routine. This assumes of course line_posted_date is a smart date.
March 06, 2006, 01:28 PM
ETHow just subtracting a year using Dialog Manager:
-SET &INPDT='03062006';
-SET &INPDTMD=EDIT(&INPDT,'9999');
-SET &INPDTYY=EDIT(&INPDT,'$$$$9999');
-SET &INPDTYY2=&INPDTYY - 1;
-SET &LASTYR=&INPDTMD | &INPDTYY2;
-TYPE CURRENT YEAR &INPDT LAST YEAR &LASTYR
gives the following results:
CURRENT YEAR 03062006 LAST YEAR 03062005
March 07, 2006, 12:56 AM
PiipsterTry single quotes around the date value:
WHERE LINE_POSTED_DATE LE DATEADD('&CY_DATE', 'Y', -1);