oracle to_date | Oracle/PLSQL: To_Date Function

Published: November 18, 2008    Posted in: Data types, Functions, Fundamentals, Oracle, SQL TipsTags:

The purpose of TO_DATE in Oracle pl/sql is to convert char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE datatype.Or more generically the Oracle to_date function is used to change a test string (or variable) into an internal date format.

Syntax
to_date( stringDate, [ format_mask ], [ nls_param ] )

Where
stringDate : is the string to convert to date
format_mask : is the format that will be used to convert stringDate to a date.
nls_param : is the nls language used to convert stringDate to a date.

Examples:

SQL> select to_date('JAN/12/2009' ,'MON-DD-YYYY HH24:MI:SS') THE_DATE from dual;
THE_DATE
1/12/2009

SQL> select to_date('20080312','YYYYMMDD') from dual;

TO_DATE('20080312','YYYYMMDD')
3/12/2008
SQL> select to_date('20080312','YYYYDDMM') from dual;

TO_DATE('20080312','YYYYDDMM')
12/3/2008

SQL> select to_date('2006/11/14 18', 'yyyy/mm/dd hh24') from dual;

TO_DATE('2006/11/1418','YYYY/M
11/14/2006 6:00:00 PM

SQL> SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American')FROM DUAL;
TO_DATE('JANUARY15,1989,11:00A
1/15/1989 11:00:00 AM
SQL>

Also Read,

Technorati Tags:
, , , , ,