Oracle TO_TIMESTAMP function


The TO_TIMESTAMP() function is used to convert CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.


TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])


Name Description
char The CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype value to be converted.
fmt | nlsparam

Specifies the format of char.
If fmt is not present, then char must be in the default format of the TIMESTAMP datatype, which is determined by the NLS_TIMESTAMP_FORMAT initialization parameter

Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

Applies to:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Pictorial Presentation of Oracle TO_TIMESTAMP function

Example: Oracle TO_TIMESTAMP function

The following example converts a character string to a timestamp. The character string is not in the default TIMESTAMP format, so the format mask must be specified:

SQL> SELECT TO_TIMESTAMP ('21-mar-15 11:20:10.123000',
      2  'DD-Mon-RR HH24:MI:SS.FF')
	  3  FROM DUAL;;

Sample Output:

 21-MAR-15 AM

Previous: TO_CHAR(datetime)

Share this Tutorial / Exercise on : Facebook and Twitter