TO_DATE

Convert an expression to a date value.

Syntax
      to_date(char[,'format'[,nls_lang])

Key
   char      String expression that will be converted to a date
   format    Date format to use.
   nls_lang  The international language to use.

to_date will convert either a character string or an expression into a date value.

The 'format' must be a valid DATE format: YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute
If no format is specified Oracle will assume the default date format has been supplied in char.

Examples

 to_date('29-Oct-09', 'DD-Mon-YY')
 to_date('10/29/09', 'MM/DD/YY')
 to_date('120109', 'MMDDYY')
 to_date('29-Oct-09', 'DD-Mon-YY HH:MI:SS') 
 to_date('Oct/29/09', 'Mon/DD/YY HH:MI:SS')
 to_date('October.29.2009', 'Month.DD.YYYY HH:MI:SS')

SQL> select * from sales where order_date > to_date('29-Oct-09', 'DD-Mon-YY');

To check that year 2000 dates are appearing correctly try the following:

SELECT 
     to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y1999,
     to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),2 * 12),'DD-MON-YYYY') y2000,
     to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),7 * 12),'DD-MON-YYYY') y2005,
     to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),52 * 12),'DD-MON-YYYY') y2050
FROM   
DUAL;

-- Expected output

-- Y1999       Y2000       Y2005       Y2050
-- ----------- ----------- ----------- -----------
-- 01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050

Related

TO_CHAR - Convert to character String
TO_NUMBER - Convert to numeric format
Oracle SQL Functions
ORA-00932 inconsistent datatypes (todate)