Here’s an example of returning the first day of a given month in Oracle Database. We can return the first day of the current month, a previous or later month, or the first day of a month based on a specified date.

Example

SELECT TRUNC(date '2035-02-15', 'MM')
FROM DUAL;

Result:

01-FEB-35

The TRUNC(date) function returns the given date value with the time portion of the day truncated to the unit provided in the specified format model. In our case, the unit we provided was MM (for the month). This can also be specified as MONTH, MON, or RM.

Note that TRUNC(date) operates according to the rules of the Gregorian calendar, and it is not sensitive to the NLS_CALENDAR session parameter. 

Also, the function always returns a DATE value, even if a different datetime data type was specified in the first argument.

Next Month

We can return the first day of next month by modifying our code like this:

SELECT 
    SYSDATE AS "Current Date",
    TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') AS "Next Month"
FROM DUAL;

Result:

Current Date	Next Month
12-APR-22	01-MAY-22

In this case we added one month to the current date.

We did this by calling the ADD_MONTHS() function, and using 1 as the second argument. This adds one month to the date provided in the first argument. We used SYSDATE for the current date.

To add two months use 2, and so on.

Another way to do it is like this:

SELECT 
    SYSDATE AS "Current Date",
    LAST_DAY(SYSDATE) + 1 AS "Next Month"
FROM DUAL;

Result:

Current Date	Next Month
12-APR-22	01-MAY-22

Here, we used the LAST_DAY() function to return the last day of the current month, then we added one day to that.

Last Month

To get the first day of last month, we can simply change the 1 to -1:

SELECT 
    SYSDATE AS "Current Date",
    TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') AS "Last Month"
FROM DUAL;

Result:

Current Date	Last Month
12-APR-22	01-MAR-22