Add

Date Functions in oracle

3:32:00 PM Rohit Kumar Verma 0 Comments

Date Functions:- 
The default date format is DD-MON-YY.
Oracle supports the following date functions.
1. sysdate
2. last_day()
3. add_months()
4. next_day()
5. months_between()

How to display current system date in oracle ?

Sysdate:-
It displays the current system date in oracle date format.ie. DD-MON-YY.
ex: 
sql> select sysdate from dual;
output:
28-04-2016

How to display last day of the month in oracle ?

last_day():-
It displays the last day of the specified month in oracle.

ex:
sql> select last_day(sysdate) from dual;
output:
30-apr-2016

How to add or substract months in oracle ?

add_months():-
It is used to add or substract the number of months of the specified date based on the second parameter.

ex:
sql> select add_months(sysdate,1) from dual;
output:
28-may-2016

ex 2:
sql> select add_months(sysdate,-1) from dual;
output:
28-mar-2016

How to display next day from the specified date ?

next_day():-
It is used to display the next occurence day from the specified date based on the second parameter.

ex: 
sql> select next_day(sysdate,'fri') from dual;
output:
29-Apr-2016

How to display the number of months in between the specified date ?

months_between():-
It is used to display the number of months in between the two specified dates.
* date1 must be greater than date2

ex:
sql> select months_between(sysdate,'10-jan-2016') from dual;
output:
3.60154981

* you can add date with any number.
* you can substract date with any number.
* you can not add two dates.
* you can substract two dates.

0 comments: