TO_DATE

Convert an expression to a date value

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

You can convert a character or an expression into a date value.

The 'format' must be a valid DATE format. If no format is specified Oracle will assume the default date format has been supplied in char.

nls_lang allows international formats to be applied.

Examples

to_date('29-Oct-05'), 'DD-Mon-YY HH:MI:SS')
to_date('Oct/29/05'), 'Mon/DD/YY HH:MI:SS')
to_date('October.29.2005'), 'Month.DD.YYYY HH:MI:SS')

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

additional query terms:
todate