Date Functions in Oracle

DATE FUNCTIONS

• ADD_MONTHS()
• MONTHS_BETWEEN()
• NEXT_DAY()
• LAST_DAY()

CONDITIONS

ACCEPT

DATE + NUMBER
DATE - NUMBER
DATE - DATE

NOT ACCEPT

DATE + DATE : ERROR

DATE

SQL> select sysdate from dual;

SYSDATE
---------
22-AUG-17

DATE + NUMBER
SQL> select sysdate+1 from dual;

SYSDATE+1
---------
23-AUG-17

DATE - NUMBER 

SQL> select sysdate-1 from dual;

SYSDATE-1
---------
21-AUG-17

DATE - DATE

SQL> select sysdate - to_date('12-aug-17','dd-mon-yy')from dual;

SYSDATE-TO_DATE('12-AUG-17','DD-MON-YY')
----------------------------------------
                              10.5595949

DATE + DATE

SQL> select sysdate + to_date('12-aug-17','dd-mon-yy')from dual;
select sysdate + to_date('12-aug-17','dd-mon-yy')from dual
               *
ERROR at line 1:
ORA-00975: date + date not allowed


ADD_MONTHS() : ADD MONTHS OF THE GIVEN DATE

SQL> select add_months(sysdate,4) from dual;

ADD_MONTH
---------
22-DEC-17

SQL> select add_months('21-aug-2017',4) from dual;

ADD_MONTH
---------
21-DEC-17

MONTHS_BETWEEN() : THIS FUNCTION USED TO FIND THE MONTHS BETWEEN THE TWO DATES

SQL> select months_between(sysdate,'16-aug-2016')from dual;

MONTHS_BETWEEN(SYSDATE,'16-AUG-2016')
-------------------------------------
                           12.2117955

SQL> select months_between('12-jan-2016','16-aug-2016')from dual;

MONTHS_BETWEEN('12-JAN-2016','16-AUG-2016')
-------------------------------------------
                                 -7.1290323

NEXT_DAY() : THIS FUNCTION USED TO FIND THE NEXT PARTICULAR DAY OF THE WEEK 

SQL> select next_day(sysdate,'friday')from dual;

NEXT_DAY(
---------
25-AUG-17

SQL> select next_day('25-aug-2017','friday')from dual;

NEXT_DAY(
---------
01-SEP-17

LAST DAY() : ITS USED TO FIND THE LAST DAY OF THE MONTHS.

SQL> select last_day('25-aug-2017')from dual;

LAST_DAY(
---------
31-AUG-17

SQL> select last_day(sysdate)from dual;

LAST_DAY(
---------
31-AUG-17