Oracle CAST Function: A Simple Guide

The CAST function in Oracle SQL is a powerful and standard way to convert a value from one data type to another.

Think of it as a universal translator for your data. If you have a string of text that is actually a number (e.g., '123'), you can use CAST to officially change its type to NUMBER. It's the ANSI SQL standard for type conversion.

What is the CAST Function in Oracle?

The CAST(value AS new_data_type) function takes any value and attempts to convert it into a different data type. This is an "explicit" conversion, meaning you are telling Oracle exactly what you want to do.

This is useful for:

• Converting a text string (VARCHAR2) into a NUMBER for mathematical calculations.
• Converting a text string (VARCHAR2) into a DATE.
• Converting a DATE into a TIMESTAMP (to add fractional seconds).
• Handling potential errors during conversion, which is a key feature.

CAST is often used as an alternative to Oracle's specific functions like TO_NUMBER and TO_DATE.

CAST Function Syntax

The syntax for CAST is:

CAST(value AS new_data_type [DEFAULT return_value ON CONVERSION ERROR])

Let's break that down:

• value: The data or column you want to convert (e.g., '500', hire_date).
• new_data_type: The target data type you want to convert to (e.g., NUMBER, DATE, TIMESTAMP).
• [DEFAULT ... ON CONVERSION ERROR] (Optional): This is a powerful feature. It lets you provide a default value (like 0 or NULL) to return if the conversion fails, instead of the query stopping with an error.

Oracle CAST Function Examples

Here are two practical examples of how to use CAST.

Example 1: Using CAST to Convert a String to a Number

This is a very common use case. Imagine you have a VARCHAR2 column that stores a price, and you need to perform math on it. CAST can convert it to a NUMBER.

This example also shows the ON CONVERSION ERROR clause to safely handle "bad" data.

Query:

WITH product_prices AS (
  SELECT '500.25' AS price_text FROM DUAL UNION ALL
  SELECT '100' AS price_text FROM DUAL UNION ALL
  SELECT 'N/A' AS price_text FROM DUAL -- This will cause an error
)
SELECT 
  price_text,
  CAST(price_text AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Converted_Price"
FROM product_prices;

Result:

PRICE_TEXT Converted_Price
---------- ---------------
500.25              500.25
100                  100
N/A                    0

Notice the 'N/A' row did not cause an error; the function returned the default value of 0.

Example 2: Using CAST to Convert a DATE to a TIMESTAMP

Another common use is to "upgrade" a DATE value to a TIMESTAMP value. A DATE in Oracle has precision only to the second. A TIMESTAMP has fractional seconds.

CAST makes this conversion simple.

Query:

SELECT 
  SYSDATE AS "Original_Date",
  CAST(SYSDATE AS TIMESTAMP) AS "Converted_Timestamp"
FROM DUAL;

Result: (The DATE value is given fractional seconds, which are set to 0.)

Original_Date        Converted_Timestamp
-------------------- -----------------------------------
07-NOV-2025 09:00:00 07-NOV-25 09.00.00.000000 AM