Oracle TO_TIMESTAMP Function: A Simple Guide

The TO_TIMESTAMP function in Oracle SQL is a powerful conversion function. Its job is to take a text string (like '2025-01-20 15:30:00') and convert it into a TIMESTAMP data type.

A TIMESTAMP is more precise than a DATE because it can also store fractional seconds. This function is essential when you need to insert or compare a string value with a TIMESTAMP column.

What is the TO_TIMESTAMP Function in Oracle?

The TO_TIMESTAMP function "translates" a character string into a TIMESTAMP. To do this, you must provide the string and a "format model" (a fmt string) that tells Oracle exactly how to read the string, including the fractional seconds.

A key modern feature of this function is the optional DEFAULT ... ON CONVERSION ERROR clause. This allows you to provide a fallback value (like NULL) if the conversion fails, which prevents your query from stopping with an error on "bad" data.

TO_TIMESTAMP Function Syntax

The syntax for TO_TIMESTAMP is:

TO_TIMESTAMP(char [DEFAULT return_value ON CONVERSION ERROR]
           [, fmt]
           [, 'nlsparam'])

Let's break that down:

• char: The text string you want to convert (e.g., '20-NOV-2025 14:30:00.123').
• DEFAULT return_value ON CONVERSION ERROR (Optional): A safety feature. It provides a default value to return if the char string doesn't match the format, preventing the query from failing.
• [fmt] (Optional, but Highly Recommended): The "format model" that tells Oracle how to read the string. This is critical for timestamps.
• [nlsparam] (Optional): A string that specifies the language for month or day names.

Common Format Models (fmt)

• YYYY-MM-DD: Standard date parts.
• HH24: Hour in 24-hour format (00-23).
• MI: Minutes (00-59).
• SS: Seconds (00-59).
• FF: Fractional Seconds. The number of Fs (e.g., FF3, FF6) can be used to match the precision. FF by itself matches any number of fractional seconds.

Best Practice: Always specify the format model (fmt) to ensure your SQL is predictable and works for everyone.

Oracle TO_TIMESTAMP Function Examples

Here are two practical examples of how to use TO_TIMESTAMP.

Example 1: Basic String-to-TIMESTAMP Conversion using TO_TIMESTAMP

This is the most common use case. We have a log string in a standard ISO format, and we want to convert it to a TIMESTAMP value.

Query:

SELECT
  TO_TIMESTAMP(
    '2025-11-20 15:30:45.123456',
    'YYYY-MM-DD HH24:MI:SS.FF'
  ) AS "Converted_Timestamp"
FROM DUAL;

Result: (The output will be a TIMESTAMP value)

Converted_Timestamp
---------------------------------------------------------------------------
20-NOV-25 03.30.45.123456000 PM

Example 2: Handling Conversion Errors with TO_TIMESTAMP

This example uses the DEFAULT ON CONVERSION ERROR clause. The string '2025-11-20 25:30:00' is invalid because the hour 25 is out of range. This would normally cause an error and stop the query.

With the DEFAULT clause, the query won't stop; it will simply return NULL instead.

Query:

SELECT
  TO_TIMESTAMP(
    '2025-11-20 25:30:00' DEFAULT NULL ON CONVERSION ERROR,
    'YYYY-MM-DD HH24:MI:SS'
  ) AS "Converted_Value"
FROM DUAL;

Result: (Instead of an ORA-01850: hour must be between 0 and 23 error, Oracle returns the default value, which is NULL.)

Converted_Value
---------------------------------------------------------------------------
(NULL)