DATE FUNCTION

ADD_MONTH function returns a date plus n months.

SELECT add_months('01-Aug-03', 3) FROM DUAL -- would return '01-Nov-03'
SELECT add_months('01-Aug-03', -3) FROM DUAL -- would return '01-May-03'
SELECT add_months('21-Aug-03', -3) FROM DUAL -- would return '21-May-03'
SELECT add_months('31-Jan-03', 1) FROM DUAL -- would return '28-Feb-03'


current_date
function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.

select current_date from dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

select current_date from dual;

ALTER SESSION SET TIME_ZONE = '5:30';


CURRENT_TIMESTAMP

select current_timestamp from dual;


from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual;
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual;


LAST_DAY

SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd')) FROM DUAL -- would return Mar 31, 2003
SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 28, 2003
SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd')) FROM DUAL -- would return Feb 29, 2004



select sessiontimezone from dual;
select localtimestamp from dual;

months_between

SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return -2.41935483870968

SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) FROM DUAL
-- would return 3.58064516129032

SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 0

SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') ) FROM DUAL
-- would return 2


new_time function returns a date in time zone1 to a date in time zone2.

SELECT new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL


NEXT_DAY function returns the first weekday that is greater than a date.
SELECT next_day('01-Aug-03', 'TUESDAY') FROM DUAL -- would return '05-Aug-03'
SELECT next_day('06-Aug-03', 'WEDNESDAY') FROM DUAL --would return '13-Aug-03'
SELECT next_day('06-Aug-03', 'SUNDAY') FROM DUAL --would return '10-Aug-03'


ROUND
SELECT round(to_date ('22-AUG-03'),'YEAR') FROM DUAL -- would return '01-JAN-04'
SELECT round(to_date ('22-AUG-03'),'Q') FROM DUAL --would return '01-OCT-03'
SELECT round(to_date ('22-AUG-03'),'MONTH') FROM DUAL --would return '01-SEP-03'
SELECT round(to_date ('22-AUG-03'),'DDD') FROM DUAL --would return '22-AUG-03'
SELECT round(to_date ('22-AUG-03'),'DAY') FROM DUAL --would return '24-AUG-03'


SELECT SYSDATE FROM DUAL

select systimestamp from dual;

to_char
SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'

to_date
SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.

trunc
SELECT trunc(to_date('22-AUG-03'), 'YEAR') FROM DUAL -- would return '01-JAN-03'
SELECT trunc(to_date('22-AUG-03'), 'Q') FROM DUAL -- would return '01-JUL-03'
SELECT trunc(to_date('22-AUG-03'), 'MONTH') FROM DUAL -- would return '01-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DDD') FROM DUAL -- would return '22-AUG-03'
SELECT trunc(to_date('22-AUG-03'), 'DAY') FROM DUAL -- would return '17-AUG-03'