Oracle - How to Get Number of Days Left in Month

Given a date, how to find out the number of days left in the specified month?

Oracle Query

The following query shows how to get the number of days left in the current month (SYSDATE returns January 11, 2012 in this example):

   SELECT LAST_DAY(SYSDATE) - SYSDATE FROM dual;
   -- Result: 20

How It Works

We used SYSDATE function to return the current date, but here you can use any datetime expression.

  • LAST_DAY

LAST_DAY function returns the last date (not just day), so in our example LAST_DAY(SYSDATE) returns January 31, 2012.

  • Datetime Arithmetic

In Oracle when you subtract one datetime value from another, the result is the number of days between them, so LAST_DAY(SYSDATE) - SYSDATE returns the required number of days left in the month.

Oracle Resources