Date Maths – Oracle

This article continues the Date Maths series and here we are using Oracle. Please refer to the previous articles on SQL Server and DB2 for similar calculations for those database systems.

Please refer to the Oracle 11g SQL Reference for more information on the date functions used in this article.

Oracle Date Functions

To add or subtract a number of days from a date we can just use plus and minus operators, for example

  sysdate + 10

will add 10 days to the current date.

To add or subtract a whole number of months we use the add_months() function. The date returned is the same day number of the month so for example

  add_months(to_date('10-JAN-2010'), 2)

will return 10 March 2010. Note, the returned value will adjust so that the date is a legitmate value, for example

  add_months(to_date(31-JAN-2010'), 1)

will return 28th February rather than 31st February.

There is no equivalent for adding or subtracting years but we can just add or subtract a multiple of 12 months.

The months_between() function calculates the number of months between two dates. When the first date is greater than the second date then the returned value is positive otherwise it is negative. When there isn’t an exact number of months between the two dates then the returned value contains a decimal where the decimal part is the number of remaining days divided by 31.  For example,

  months_between(to_date('10-MAR-2010'), to_date('01-JAN-2010')) "Months Between"

will returns 2.2903… We can remove the the decimal part using floor (to round down):

  floor(months_between(to_date('10-MAR-2010'), to_date('01-JAN-2010'))) "Even Months Between"

will return 2 which we can interpret as there being two whole months between the two dates.

Oracle Date Calculations

The following are example of some common date calculations,

Current Week


Current Month


Current Year


First Day of Current Week


Last Day of Current Week


First Day of Previous Week


Last Day of Previous Week


First Day of Next Week


Last Day of Next Week


First Day of Current Month


Last Day of Current Month


First Day of Previous Month


Last Day of Previous Month


First Day of Next Month


Last Day of Next Month


First Day of Current Year

  trunc(sysdate, 'YYYY')

Last Day of Current Year

  add_months(trunc(sysdate, 'YYYY'),  12)-1

First Day of Previous Year

  add_months(trunc(sysdate, 'YYYY'), -12)

Last Day of Previous Year

  trunc(sysdate, 'YYYY')-1

First Day of Next Year

  add_months(trunc(sysdate, 'YYYY'),  12)

Last Day of Next Year

  add_months(trunc(sysdate, 'YYYY'),  24)-1

2 thoughts on “Date Maths – Oracle”

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s