Oracle TO_TIMESTAMP Function with Examples

calendar Oct 8, 2015 · 3 min read · sql-functions  ·
Share on: linkedin copy

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:

1TO_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.

1SELECT
2TO_CHAR(TRUNC(SYSTIMESTAMP))
3FROM dual;

Result:

117-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.

1SELECT
2TO_TIMESTAMP('16-SEP-2022 08:10:14 AM')
3FROM dual;

Result:

116/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.

1SELECT
2TO_TIMESTAMP('12-09-2012 04:32:20 PM', 'DD-MM-YYYY HH:MI:SS AM') AS TS_TEST
3FROM dual;

Result:

112/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.

1SELECT
2TO_TIMESTAMP('8 DEC 2022 10:04', 'DD MON YYYY HH:MI') AS TS_TEST
3FROM dual;

Result:

108/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:

comments powered by Disqus