The Oracle TO_TIMESTAMP function is useful if you need to work with timestamp data types. Learn how to use it with some examples in this article.
Purpose of the Oracle TO_TIMESTAMP Function
The purpose of the Oracle TO_TIMESTAMP function is to convert a string into a timestamp. It’s the “string to timestamp” function in Oracle SQL.
Now, a TIMESTAMP value is a type of date. It includes:
- the date,
- the time, and
- a series of decimal places to indicate fractional seconds.
Converting a string to a TIMESTAMP value is done using the TO_TIMESTAMP function.
Syntax
The syntax of the TO_TIMESTAMP function is:
TO_TIMESTAMP (input_string, [format_mask], ['nlsparam'] )
Parameters
The parameters of the Oracle TO_TIMESTAMP function are:
- input_string (mandatory): This is the input string, or the string to convert into the timestamp value.
- format_mask (optional): This string specifies the format of the input_string, as you can specify your input in many different ways. If this is not supplied, then the input_string must be in the same format as the NLS_TIMESTAMP_FORMAT parameter, which is your default format.
- nlsparam (optional): If this string is supplied, you can specify the language in which the month and day names and abbreviations are returned.
Can You Convert an Oracle TIMESTAMP to a CHAR?
Yes, you can, if you really need to.
You can use the TO_CHAR function, along with TRUNC, and specify the format.
SELECT
TO_CHAR(TRUNC(SYSTIMESTAMP))
FROM dual;
Result:
17-09-2022
This result is in a CHAR format. You can use a similar set of functions to convert your TIMESTAMP values to CHAR.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
What Format Do I Need To Supply For Oracle TO_TIMESTAMP?
The default format of the TO_TIMESTAMP function is the value of NLS_TIMESTAMP_FORMAT, which depends on your Oracle database.
The format can be something like this: ‘YYYY-MM-DD HH:MI:SS.FF’
The different formats you can supply are:
Parameter | Explanation |
YYYY | Four digit year |
MM | Month (01-12, where JAN = 01) |
MON | Abbreviated name of month |
MONTH | Name of month, padded with blanks to length of 9 characters |
DD | Day of month (1-31) |
HH | Hour of day (1-12) |
HH12 | Hour of day (1-12) |
HH24 | Hour of day (0-23) |
MI | Minute (0-59) |
SS | Second (0-59) |
Examples of the Oracle TO_TIMESTAMP Function
Here are some examples of the TO_TIMESTAMP function to convert a string to a timestamp.
I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This example uses the TO_TIMESTAMP function with the default format.
SELECT
TO_TIMESTAMP('16-SEP-2022 08:10:14 AM')
FROM dual;
Result:
16/SEP/22 08:10:14.000000000 AM
The result is the same value but converted to a TIMESTAMP data type.
Example 2
This example uses the TO_TIMESTAMP function, but we specify a format.
SELECT
TO_TIMESTAMP('12-09-2012 04:32:20 PM', 'DD-MM-YYYY HH:MI:SS AM') AS TS_TEST
FROM dual;
Result:
12/SEP/22 04:32:20.000000000 PM
The result is the same value but converted to a TIMESTAMP data type.
Example 3
This example uses the TO_TIMESTAMP function, but we specify a different format.
SELECT
TO_TIMESTAMP('8 DEC 2022 10:04', 'DD MON YYYY HH:MI') AS TS_TEST
FROM dual;
Result:
08/DEC/22 10:04:00.000000000 AM
The result is the same value but converted to a TIMESTAMP data type.
Similar Functions
Some functions which are similar to the TO_TIMESTAMP function are:
- TO_DATE: This function converts a value into a DATE data type, which is similar, but has no time component.
You can find a full list of Oracle SQL functions here.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
TO_TIMESTAMP not giving Full Year 2015. Even after proving YYYY mask. Why?
Hi, what does your SQL look like? It could be to do with the input data, perhaps it’s in a strange format.
HI MK RAO,
You just replace your ‘YYYY’ to ‘RR’.
This will help your problem.
thank you
how to convert varchar2 data like ‘1569996880844’ o date timestamp in oracle ?
say if i wanted to be in this format – ‘DD-MM-YYYY HH24:MM:SS’.
what is the datatype used to store date, timing including millisecond. i.e yyyy/mm/dd hh:mi:ss.fff