I need to get the last 3 full months of data so March, April, and May in this case. This almost gives me what I want but it includes June 1st which I don't need. I need March 1st through May 31st.

Oracle SQL:

where d.D_DATE between add_months(trunc(sysdate, 'month'), -3) and add_months(trunc(sysdate, 'month'),0)

CodePudding user response：

Got it. where d.D_DATE between add_months(trunc(sysdate, 'month'), -3) and last_day(add_months(trunc(sysdate, 'month'),-1))

CodePudding user response：

Use >= and < rather than BETWEEN:

SELECT *
FROM   d
WHERE  d.D_DATE >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
AND    d.D_DATE <  TRUNC(SYSDATE, 'MM')

Which, for the sample data:

CREATE TABLE d (D_DATE) AS
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3) - INTERVAL '1' SECOND FROM DUAL UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3) FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'MM') - INTERVAL '1' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'MM') - INTERVAL '1' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'MM') - INTERVAL '1' DAY   INTERVAL '1' SECOND FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'MM') - INTERVAL '1' SECOND FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;

Outputs:

D_DATE
2022-03-01 00:00:00
2022-05-31 00:00:00
2022-05-31 00:00:00
2022-05-31 00:00:01
2022-05-31 23:59:59

db<>fiddle here