Search code examples
sqldatehanadate-arithmetic

How to Query Last 90 Days in Hana SQL (Subtract from Current Date)


I have the following query:

SELECT "SURF_RT"."SALES_REQUEST"."SYS_ID","SURF_RT"."U_EMPLOYEE"."DV_U_EMPLOYEE_MANAGER", "SURF_RT"."SALES_ACCOUNT"."DV_PRIMARY_SALES_REP","SURF_RT"."SALES_REQUEST"."NUMBER","SALES_REQUEST"."DV_SALES_ACCOUNT",
          "SALES_REQUEST"."DV_STATE","OPENED_AT","U_ANALYZED_DATE", "U_USD_TOTAL_POTENTIAL_NNACV",  MAX("END_DATE") AS "ContractEnd"
FROM "SURF_RT"."SALES_REQUEST"
JOIN "SURF_RT"."SALES_ACCOUNT" ON "SURF_RT"."SALES_ACCOUNT"."NAME"="SURF_RT"."SALES_REQUEST"."DV_SALES_ACCOUNT"
JOIN "SURF_RT"."U_EMPLOYEE" ON "SURF_RT"."U_EMPLOYEE"."NAME"="SURF_RT"."SALES_ACCOUNT"."DV_PRIMARY_SALES_REP"
left join "SURF_RT"."SALES_CONTRACT"  on "SURF_RT"."SALES_CONTRACT"."DV_ACCOUNT"="SURF_RT"."SALES_REQUEST"."DV_SALES_ACCOUNT"
WHERE "DV_SALES_SUBCATEGORY" = 'Reporting Request'
AND "OPENED_AT" >= CURRENT_DATE-90
GROUP BY "SURF_RT"."SALES_REQUEST"."SYS_ID","SURF_RT"."U_EMPLOYEE"."DV_U_EMPLOYEE_MANAGER", "SURF_RT"."SALES_ACCOUNT"."DV_PRIMARY_SALES_REP","SURF_RT"."SALES_REQUEST"."NUMBER","SALES_REQUEST"."DV_SALES_ACCOUNT",
          "SALES_REQUEST"."DV_STATE","OPENED_AT","U_ANALYZED_DATE", "U_USD_TOTAL_POTENTIAL_NNACV"

But I am getting this error:

Could not execute 'SELECT "SURF_RT"."SALES_REQUEST"."SYS_ID","SURF_RT"."U_EMPLOYEE"."DV_U_EMPLOYEE_MANAGER", ...'
SAP DBTech JDBC: [266]: inconsistent datatype: the expression has incomputable datatype: DATE is invalid for subtraction operator: line 11 col 33 (at pos 1014)

How can I subtract from a date in Hana SQL?


Solution

  • You can use add_days():

    "OPENED_AT" >= add_days(CURRENT_DATE, -90)