TO_TIMESTAMP
The TO_TIMESTAMP function converts a CHAR, VARCHAR2, CLOB, or NCLOB argument to a value of TIMESTAMP data type.
SQL syntax
TO_TIMESTAMP (Expression1[,Expression2[,Expression3]])
Parameters
TO_TIMESTAMP has the parameters:
| Parameter | Description |
|---|---|
|
|
A |
|
|
The format string that specifies the format of |
|
|
A |
Description
-
The
TO_TIMESTAMPfunction converts aCHAR,VARCHAR2,CLOB, orNCLOBexpression (passed to the function asExpression1) to a value of theTIMESTAMPdata type. The return data type isTIMESTAMP. -
You can use a valid datetime format element for the format string in
Expression2. See "Datetime Format Models" for more information.
Examples
Example 1: This example shows the return data type for the TO_TIMESTAMP function, which has the maximum fractional second precision of 9.
Command> describe SELECT TO_TIMESTAMP ('2021-05-07 10:11:12.123456') FROM dual;
Prepared Statement:
Columns:
EXP TIMESTAMP (9) NOT NULL
Example 2: This example throws an error when converting the character string to the TIMESTAMP data type. Expression1 indicates a fractional second precision of 6 for the TIMESTAMP data type, but the format string (Expression2) indicates a value of 2 (FF2). The value cannot be truncated, resulting in a conversion error.
Command> SELECT TO_TIMESTAMP('2021-01-01 10:11:12.123456',
'YYYY-MM-DD HH:MI:SS.FF2') FROM dual;
2813: Error converting from character string '2021-01-01 10:11:12.123456'
to timestamp
The command failed.
Example 3: These examples illustrate that the value of n for FF[n] should be large enough to accommodate the fractional seconds of Expression1 (123456, in this example), such that there is no truncation. If you do not specify a value for n, as in the second example, the default is 9.
Command> SELECT TO_TIMESTAMP ('2021-05-07 10:10:10.123456',
'YYYY-MM-DD HH:MI:SS.FF6') FROM dual;
< 2021-05-07 10:10:10.123456 >
1 row found.
Command> select to_timestamp('2021-05-07 10:10:10.123456',
'YYYY-MM-DD HH:MI:SS.FF') FROM dual;
< 2021-05-07 10:10:10.123456000 >
1 row found.
Example 4: These examples show the result when Expression1 is a character string and a format string is specified.
Command> SELECT TO_TIMESTAMP ('2021-05-07 10:10:10.123456',
'YYYY-MM-DD HH:MI:SS.FF6') FROM dual;
< 2021-05-07 10:10:10.123456 >
1 row found.
Command> SELECT TO_TIMESTAMP ('2021-05-07 23:00:00.123456',
'YYYY-MM-DD HH24:MI:SS.FF6') FROM dual;
< 2021-05-07 23:00:00.123456 >
1 row found.
Example 5: This example uses the FF format string. The FF format uses the maximum precision of 9 as shown in the result.
Command> SELECT TO_TIMESTAMP ('10-Sep-02 10:10:10.123000',
'DD-Mon-RR HH12:MI:SS.FF') FROM dual;
< 2002-09-10 10:10:10.123000000 >
1 row found.
Example 6: These examples show the result when there is no format string. The default format is used for Expression1. Note that the fractional seconds precision of Expression1 (123456789, in this example) is optional.
Command> SELECT TO_TIMESTAMP ('2021-MAY-07 101010.123456789') FROM dual;
< 2021-05-07 10:10:10.123456789 >
1 row found.
Command> SELECT TO_TIMESTAMP ('2021-MAY-07 101010') FROM dual;
< 2021-05-07 10:10:10.000000000 >
1 row found.
Command> SELECT TO_TIMESTAMP ('2021-MAY-07 101010.12') FROM dual;
< 2021-05-07 10:10:10.120000000 >
1 row found.
Example 7: This example illustrates the usage of the TO_CHAR function with the TO_TIMESTAMP to return the TIMESTAMP data type in an explicit timestamp format.
Command> SELECT TO_CHAR(TO_TIMESTAMP ('2021-05-07 13:11:12.123456',
'YYYY-MM-DD HH24:MI:SS.FF6'),'DD/MON/YYYY HH24:MI:SS.FF6 PM') FROM dual;
< 07/MAY/2021 13:11:12.123456 PM >
1 row found.
Example 8: This example creates a table with a column of type TIMESTAMP(9). After describing the table, the example inserts one row without using the TO_TIMESTAMP function, and then inserts a second row using the TO_TIMESTAMP function. A SELECT on the table shows the results from the two insert operations.
Command> CREATE TABLE ts_table(col1 TIMESTAMP(9));
Command> describe ts_table;
Table SAMPLEUSER.TS_TABLE:
Columns:
COL1 TIMESTAMP (9)
1 table found.
(primary key columns are indicated with *)
Command> INSERT INTO ts_table VALUES('2021-05-04 11:12:13.999999');
1 row inserted.
Command> INSERT INTO ts_table VALUES(to_timestamp('04-05-2021 11:12:13.123456789',
'DD-MM-YYYY HH:MI:SS.FF9'));
1 row inserted.
Command> SELECT * FROM ts_table;
< 2021-05-04 11:12:13.999999000 >
< 2021-05-04 11:12:13.123456789 >
2 rows found.
Example 9: These examples illustrate the use of the AM and the PM format strings.
Command> SELECT TO_TIMESTAMP ('10-Sep-02 10:10:10.123456 PM',
'DD-Mon-RR HH12:MI:SS.FF6 PM') FROM dual;
< 2002-09-10 22:10:10.123456 >
1 row found.
Command> SELECT TO_CHAR(TO_TIMESTAMP ('10-Sep-02 10:10:10.123456 PM',
'DD-Mon-RR HH12:MI:SS.FF6 PM'),'DD-Mon-RR HH12:MI:SS.FF6 PM')
FROM dual;
< 10-Sep-02 10:10:10.123456 PM >
1 row found.
The following example creates the ts_table2 table, defining col2 with the TIMESTAMP(9) data type. After describing the table, insert operations are done, illustrating the use of inserting data into a TIMESTAMP column using AM and PM.
Command> CREATE TABLE ts_table2 (col1 number primary key, col2 timestamp(9));
Command> describe ts_table2;
Table SAMPLEUSER.TS_TABLE2:
Columns:
*COL1 NUMBER NOT NULL
COL2 TIMESTAMP (9)
1 table found.
(primary key columns are indicated with *)
Command> INSERT INTO ts_table2 VALUES (100,
TO_TIMESTAMP('10-FEB-20 12.46.48.802050 PM',
'DD-MON-RR HH:MI:SS.FF AM'));
1 row inserted.
Command> SELECT TO_CHAR(col2) FROM ts_table2;
< 2021-02-10 12:46:48.802050000 >
1 row found.
Command> SELECT TO_CHAR (col2, 'DD-MON-RR HH:MI:SS.FF AM') from ts_table2;
< 10-FEB-20 12:46:48.802050000 PM >
1 row found.