1. What can we do with Oracle date functions ?
We can get
(i) current system date (date, date & time)
(ii) current session date (date, date & time)
(iii) Add months to a date.
(iv) Extract day, month, year from a date.
(v) Last day of the Month. (monday, tuesday etc..)
(vi) Next day.
(vii) Difference between two date's and return months, days etc..
(viii) ROUND, TRUNC the date to apprx value.
(i) current system date (date, date & time) & current session date (date, date & time)
=> CURRENT_DATE 12-FEB-12
=> CURRENT_TIMESTAMP 12-FEB-12 08.44.09.943000 PM +05:30
=> DBTIMEZONE +00:00
=> LOCALTIMESTAMP 12-FEB-12 08.45.48.553000 PM
=> SESSIONTIMEZONE +05:30
=> SYSDATE 12-FEB-12
=> SYSTIMESTAMP 12-FEB-12 08.46.40.528000 PM +05:30
(ii) Add months to a date.
We can perform :
x Date1 + Date2 Invalid
Date1 - Date2 valid
Date1 + number valid
Date1 - number valid
To add months to a date, we can use "ADD_MONTHS" function.
=> SYSDATE + 5
=> SYSDATE -10
=> SYSDATE + '12-DEC-12' NOT VALID
=> Select SYSDATE + HIREDATE From EMP;
=> Select sysdate, ADD_MONTHS(sysdate, 2) From Dual; -- 12-FEB-12, 12-APR-12
=> Select sysdate, ADD_MONTHS(sysdate, -4) From Dual; -- 12-FEB-12, 12-OCT-11
(iii) Extract day, month, year from a date.
=> Select EXTRACT (day from sysdate) From Dual; 12
=> Select EXTRACT (month from sysdate) From Dual; DEC
=> Select EXTRACT (year from sysdate) From Dual; 2012
(iv) Last day of the Month. (monday, tuesday etc..)
=> Select last_day( sysdate ) from dual; 29-FEB-12
=> Select last_day( sysdate ) +1 from dual; 01-MAR-12
(v) Next Day
=> Select next_day( sysdate, 'monday' ) from dual; 13-FEB-12
=> Select next_day( last_day(sysdate) -7, 'sunday') from dual; 26-FEB-12
(vi) Difference between two date's and return months, days etc..
=> Select MONTHS_BETWEEN( '12-DEC-12', '01-JAN-12') From Dual; 11 months.
Note:
1. In date function, Months_Between and Extract (day, year) returns Number as output.