Working with DATE

SYSDATE is a function that returns both DATE and TIME. Default format is DD-MON-YY

SQL> select sysdate from dual;
SYSDATE
---------
14-JAN-09

Date Functions:
MONTHS_BETWEEN: Number of months between two dates
ADD_MONTHS: Add calendar months to date
NEXT_DAY: Next day of the date specified
LAST_DAY: Last day of the month
ROUND: Round date
TRUNC: Truncate date

SQL> select months_between('01-JAN-08','01-MAR-08') from dual;
MONTHS_BETWEEN('01-JAN-08','01-MAR-08')
---------------------------------------
-2

Add 6 months to the date specified:
SQL> select add_months('01-FEB-08',6) from dual;
ADD_MONTH
---------
01-AUG-08

Get next coming tuesday from 14-JAN-09:
SQL> select next_day('14-JAN-09','TUESDAY') from dual;
NEXT_DAY(
---------
20-JAN-09

Retrieve the last day in a particular month:
SQL> select last_day('02-FEB-09') from dual;
LAST_DAY(
---------
28-FEB-09