SQL CAST

What does the SQL CAST function do?

Explicitly converts a value from one data type to another.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CAST:

SQL CAST Full Explanation

CAST is a type-conversion function defined in the SQL standard that creates a value of a specified data type from a given expression. Unlike implicit type coercion performed automatically by the database, CAST is explicit, making queries self-documenting and less error-prone. It can be used in SELECT lists, WHERE clauses, JOIN conditions, INSERT statements, and anywhere an expression is valid. If the requested conversion is not supported (for example, casting a non-numeric string to INTEGER), the statement fails at runtime with a conversion error. Some dialects allow synonym functions such as CONVERT or the :: operator (PostgreSQL). Precision and scale attributes (e.g., DECIMAL(10,2)) or character length (VARCHAR(50)) can be supplied. Casting never mutates stored data; it only affects the value returned in the result set or inserted into another column.

SQL CAST Syntax

CAST ( expression AS target_data_type )

SQL CAST Parameters

expression - Any valid SQL expression to be converted
target_data_type - The destination type, optionally with precision/scale or length (e.g., DECIMAL(10,2), VARCHAR(100))

Example Queries Using SQL CAST

-- String to integer
SELECT CAST('42' AS INTEGER) AS answer;

-- Decimal to varchar for concatenation
SELECT 'Price: ' || CAST(unit_price AS VARCHAR(10)) AS label
FROM products;

-- Date to year integer
SELECT order_id,
       CAST(EXTRACT(year FROM order_date) AS INTEGER) AS order_year
FROM orders;

-- Insert with casting
INSERT INTO audit_log(user_id, event_ts)
SELECT id, CAST(created_at AS TIMESTAMP)
FROM staging_users;

Expected Output Using SQL CAST

Each query returns the same underlying value but represented in the requested data type
If conversion is impossible, the database raises a runtime error

Use Cases with SQL CAST

Formatting numbers or dates as strings for reporting
Converting string parameters to numeric types in stored procedures
Aligning data types in UNION or JOIN operations
Inserting data from staging tables with relaxed types into strongly typed target tables
Ensuring deterministic behavior across dialects by avoiding implicit casts

Common Mistakes with SQL CAST

Omitting length or precision, leading to default sizes that truncate data
Assuming CAST will automatically round; most databases will raise an error on overflow
Using incompatible target types (e.g., CAST('abc' AS INT))
Forgetting that CAST affects only the query result, not the underlying column definition

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between CAST and implicit conversion?

Implicit conversion is done automatically by the database, which can hide errors. CAST requires you to state the desired type, making code clearer and safer.

Can I specify precision and scale with CAST?

Yes. For example, CAST(total AS DECIMAL(10,2)) converts a value to a decimal with 10 digits in total and 2 after the decimal point.

How do I avoid errors when casting strings to numbers?

Validate or clean the data first, or use SAFE_CAST/TRY_CAST variants available in some dialects to return NULL instead of throwing an error.

Is CAST portable across databases?

CAST is defined in the SQL standard and works in all major systems, but supported data types and default precisions can differ, so always test.