Oracle TO_TIMESTAMP Function with Examples

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.

 

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.