Focal Point
Dates, date functions and where statements

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2691005751

March 06, 2006, 12:07 PM
Bethany
Dates, 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.


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
March 06, 2006, 12:33 PM
Leah
You 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.


Leah
March 06, 2006, 01:28 PM
ET
How 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


FOCUS 7.6 MVS PDF,HTML,EXCEL
March 07, 2006, 12:56 AM
Piipster
Try single quotes around the date value:

WHERE LINE_POSTED_DATE LE DATEADD('&CY_DATE', 'Y', -1);


ttfn, kp


Access to most releases from R52x, on multiple platforms.