DateAdd function in Oracle SQL

How can I convert DATEADD(week,-4,GETDATE())in the where clause ( in Oracle SQL)

Trying to get data for prior 4 weeks

SELECT DISTINCT LOWER(C.EMAIL) AS EMAIL, FOI.ORDER_ID AS ORDERS
  FROM MTEPWDATA_CAN.MTEP_FACT_ORDER_ITEMS FOI
  LEFT JOIN MTEPWDATA_CAN.MTEP_DIM_DAY D
    ON FOI.ORDER_DATE_SKEY = D.DAY_SKEY
  LEFT JOIN MTEPWDATA_CAN.MTEP_DIM_CUSTOMER C
    ON FOI.CUSTOMER_SKEY = C.CUSTOMER_SKEY
 WHERE ((QUANTITY_ORDERED > 0 
   AND FOI.FULFILLMENT_TYPE = 'SCHEDULED_HOME'))
   AND DATEADD(week, -4, GETDATE())

​x
 
SELECT DISTINCT LOWER(C.EMAIL) AS EMAIL, FOI.ORDER_ID AS ORDERS  FROM MTEPWDATA_CAN.MTEP_FACT_ORDER_ITEMS FOI  LEFT JOIN MTEPWDATA_CAN.MTEP_DIM_DAY D    ON FOI.ORDER_DATE_SKEY = D.DAY_SKEY  LEFT JOIN MTEPWDATA_CAN.MTEP_DIM_CUSTOMER C    ON FOI.CUSTOMER_SKEY = C.CUSTOMER_SKEY WHERE ((QUANTITY_ORDERED > 0    AND FOI.FULFILLMENT_TYPE = 'SCHEDULED_HOME'))   AND DATEADD(week, -4, GETDATE())​

Answer

In Oracle, you would use:

select sysdate - interval '28' day

 
select sysdate - interval '28' day​

Or, if you prefer:

select sysdate - 4 * interval '7' day

 
select sysdate - 4 * interval '7' day​

Advertisement

Answer