Revision history [back]

There are several methods to retrieve the initial and final date of the preceding month in Oracle:

1. Using the SYSDATE function, subtracting the number of days in the current month and adding one day to get the initial date of the preceding month. Then, subtracting one day from the initial date of the current month to get the final date of the preceding month.

   SELECT TRUNC(SYSDATE, 'MM') - 1 AS LastDayOfPrecedingMonth, TRUNC(SYSDATE, 'MM') - EXTRACT(DAY FROM SYSDATE) + 1 AS FirstDayOfPrecedingMonth FROM DUAL;

2. Using the ADDMONTHS function, subtracting one month and using the LASTDAY function to get the final date of the preceding month. Then, subtracting the number of days in the preceding month minus one from the final date to get the initial date of the preceding month.

   SELECT LASTDAY(ADDMONTHS(SYSDATE, -1)) AS LastDayOfPrecedingMonth, LASTDAY(ADDMONTHS(SYSDATE, -1)) - (TONUMBER(TOCHAR(LASTDAY(ADDMONTHS(SYSDATE, -1)), 'DD')) - 1) AS FirstDayOfPrecedingMonth FROM DUAL;

Both methods will return the same results.