http://ss64.com/ora/syntax-to_date.html

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');