DATE Functions:
MONTHS_BETWEEN(date1, date2) - Returns number of months between date1 and date2
-
SELECT MONTHS_BETWEEN('01-JAN-2007', SYSDATE) FROM dual -- returns -8.43767398446834 (Here SYSDATE is 14-SEP-2007)
-
ADD_MONTHS(date1,m) - Returns date with m number of months added to date1
-
SELECT ADD_MONTHS('08-JAN-2007',2) FROM dual -- returns '08-MAR-2007'
-
LAST_DAY(date1) - Returns last day(date) of the month of date1
-
SELECT LAST_DAY('08-SEP-2007') FROM dual -- returns '30-SEP-2007'
-
NEXT_DAY(date1,m) - Returns mth day from date1
-
SELECT NEXT_DAY('08-SEP-2007',1) FROM dual -- returns '09-SEP-2007'
-
In the above example, '08-SEP-2007' is Saturday and number 1 represents the day of the week ie Sunday here. So the above query returns Sunday's date after '08-SEP-2007' ie '09-SEP-2007'
Days of the Week that can be used in NEXT_DAY function:
1 - Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thrusday
6 - Friday
7 - Saturday
ROUND(date1) - Returns Next day (date) of date1 if the time elapsed for the date1 is more than 12 hours.
-
SELECT ROUND(SYSDATE) FROM dual -- returns '15-SEP-2007' (Here SYSDATE is '9/14/2007 2:00:27 PM')
-
TRUNC(date1) - Returns Current day of date1 after truncating TIME value from date1.
-
SELECT TRUNC(SYSDATE) FROM dual -- returns '14-SEP-2007' (Here SYSDATE is '9/14/2007 2:00:27 PM')
-
