Search code examples
sqloracle

Date function not returning expected Month

I have the below SQL in Oracle DB that I am expecting would return March 1st, 2024 (01-MAR-24) however it is instead returning 01-AUG-24

SELECT TRUNC(TRUNC (SYSDATE, 'MM') - 6, 'MM')
FROM dual

Can anyone tell me why this is behaving this way? I want to select the first day of the month 'X' number of months ago and format as above but this is not behaving as I expect.

Solution

  • You are truncating the date to the start of the month then subtracting 6 days (not 6 months) and finally, truncating the value (which is now in the previous month) to the start of that month.

    What you want is

    SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6) AS dt FROM DUAL;

    or:

    SELECT TRUNC(SYSDATE, 'MM') - INTERVAL '6' MONTH AS dt FROM DUAL;

    Which both output:

    DT
    2024-03-01 00:00:00

    fiddle