Oracle TO_TIMESTAMP function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Description

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

Syntax:

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

Parameters:

Name	Description
char	The CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype value to be converted.
fmt \| nlsparam (Optional)	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

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:

TO_TIMESTAMP('21-MAR-1511:20:10.123000','DD-MON-RRHH24:MI:SS.FF')   
-----------------------------------------------------------------   
 21-MAR-15 11.20.10.123000000 AM

Previous: TO_CHAR(datetime)
Next: TO_TIMESTAMP_TZ