Oracle

# 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.

### 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`