Oracle TO_TIMESTAMP function

Last update on September 24 2024 07:29:20 (UTC/GMT +8 hours)

Description

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

Uses of Oracle TO_TIMESTAMP() Function

Date-Time Conversion: Convert string representations of date and time into timestamp values.

Data Consistency: Standardize varying date formats into a uniform timestamp format.

Time Calculations: Enable precise calculations and comparisons between date-time values.

Data Storage: Store timestamp data in a consistent and queryable format in the database.

Report Generation: Format and manipulate time-related data for reports and analytics.

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