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

  to_number(to_char(sysdate,'ww'))

Current Month

  to_number(to_char(sysdate,'mm'))

Current Year

  to_number(to_char(sysdate,'yyyy'))

First Day of Current Week

  next_day(sysdate-7,'MON')

Last Day of Current Week

  next_day(sysdate-1,'SUN')

First Day of Previous Week

  next_day(sysdate-14,'MON')

Last Day of Previous Week

  next_day(sysdate-8,'SUN')

First Day of Next Week

  next_day(sysdate,'MON')

Last Day of Next Week

  next_day(sysdate+6,'SUN')

First Day of Current Month

  last_day(add_months(sysdate,-1))+1

Last Day of Current Month

  last_day(sysdate)

First Day of Previous Month

  last_day(add_months(sysdate,-2))+1

Last Day of Previous Month

  last_day(add_months(sysdate,-1))

First Day of Next Month

  last_day(sysdate)+1

Last Day of Next Month

  last_day(add_months(sysdate,1))

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