2 Ways to Get the Number of Days in a Month in Oracle

Here are a couple of ways to return the number of days in a given month in Oracle Database. This could be the number of days in the current month, or the number of days in a month based on a specified date.

Option 1

Here’s our first option:

SELECT CAST(TO_CHAR(LAST_DAY(date '2030-01-17'), 'DD') AS INT)
FROM DUAL;

Result:

The LAST_DAY() function returns the last day of the month based on the specified date. By passing this to TO_CHAR() with a format of DD for the day of the month, we get the number of the last day of the month. That number represents the number of days in the month.

We then use the CAST() function to convert the result to an integer.

Option 2

Here’s another option:

SELECT 
    TRUNC(LAST_DAY(date '2030-08-20'))-TRUNC(date '2030-08-20', 'MM')
FROM DUAL;

Result:

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.