SQL CAST(): Syntax, Usage, and Examples

The SQL CAST function lets you convert a value from one data type to another within a query. It’s especially useful when working with mixed-type data or when formatting query results for reporting and calculations.

How to Use the SQL CAST Function

Use the SQL CAST function by writing CAST(expression AS target_data_type). The expression is what you want to convert, and target_data_type is the new type you want the result to have.

SELECT CAST(10.75 AS INT) AS rounded_value;

Use SQL CAST when you need explicit control over how data types change inside your query results.

When to Use the CAST Function in SQL

The CAST SQL function becomes helpful any time your data types don’t line up with what your query or business logic expects.

Convert Numbers to Integers

Use CAST when you need to strip decimal places from floating-point numbers. This is useful in reports, billing statements, or rounding down to whole units.

Format Dates and Times

Use the CAST function in SQL to convert a DATETIME or TIMESTAMP column into a VARCHAR string for display or logging purposes. You can also go the other direction—casting strings into dates for filtering.

Change Text to Numbers

Use SQL CAST when a numeric value is stored as text and you want to run math operations or comparisons. This happens often with imported CSVs or loosely typed external systems.

Combine Datasets With Different Types

Use CAST in SQL queries where you're combining columns from different sources—like a UNION between a string and an integer column. Casting ensures consistent types.

Examples of SQL CAST Function

Let’s walk through common examples of how to use the CAST function in SQL for real-world problems.

Round Down a Decimal to an Integer

SELECT CAST(12.99 AS INT) AS total_items;

Use this to display rounded quantities or to simplify totals.

Convert a Date to a String

SELECT CAST(order_date AS VARCHAR(10)) AS date_string
FROM orders;

This is useful when exporting reports or generating logs that require a formatted date string.

Parse a String to a Number

SELECT CAST('1500' AS DECIMAL(10, 2)) AS price;

Use this when your data is stored as a string but you want to do numeric calculations like SUM or AVG.

Combine Columns in a UNION Query

SELECT product_id, CAST(stock AS VARCHAR) AS stock_level
FROM warehouse_a
UNION
SELECT item_id, quantity
FROM warehouse_b;

Use CAST to align data types across columns from different sources.

Convert a String to a DateTime

SELECT CAST('2024-04-15' AS DATETIME) AS order_time;

Use this when filtering or calculating time differences from user input or imported CSVs.

Learn More About the SQL CAST Function

CAST vs CONVERT in SQL

You can convert data using either CAST or CONVERT in SQL. CAST follows the SQL standard and works across more databases, while CONVERT is specific to SQL Server and provides more formatting control.

-- Using CAST
SELECT CAST('2024-01-01' AS DATETIME);

-- Using CONVERT (SQL Server)
SELECT CONVERT(DATETIME, '2024-01-01', 102);

Use CAST SQL when you want portability across different systems.

Casting in Expressions

CAST isn’t limited to SELECT statements. You can also use it in WHERE clauses, JOIN conditions, and ORDER BY statements.

SELECT name
FROM employees
WHERE CAST(hire_date AS DATE) = '2023-01-01';

Use this pattern when the column is stored with a time component but you want to match by date only.

CAST in Aggregation

Sometimes aggregate functions like AVG return data types you don’t want. CAST helps with formatting the result:

SELECT CAST(AVG(salary) AS DECIMAL(10, 2)) AS average_salary
FROM employees;

Use this to keep your result columns clean and consistently formatted.

SQL CAST with JOINs

Join tables that have mismatched data types using CAST in the ON condition:

SELECT *
FROM products p
JOIN inventory i
ON p.product_id = CAST(i.item_id AS INT);

Use this when you need to force compatible types in relational logic.

SQL CAST to Decimal

You can cast to decimal values with specific precision and scale:

SELECT CAST(123 AS DECIMAL(6,2)) AS result;

This creates a decimal with 6 total digits, including 2 after the decimal point.

Use this when working with financial data or any field that requires exact decimal places.

SQL CAST to String

Use CAST(column AS VARCHAR(n)) to convert values into readable strings. Be mindful of the maximum length (n), or your values might get cut off.

SELECT CAST(employee_id AS VARCHAR(10)) + '-' + name AS id_name
FROM employees;

Use this for creating identifiers or output formatting.

CASTing Dates to Strings in SQL Server

In SQL Server, you can cast datetime to a string, but you may also prefer CONVERT for format control. Still, CAST is cleaner when the default format works for your case.

SELECT CAST(GETDATE() AS VARCHAR(20)) AS date_string;

Use this when you need to display the current date in logs or UI exports.

CAST SQL for Dynamic Filtering

In queries that rely on dynamic filtering, CAST ensures the comparison types align:

SELECT *
FROM sales
WHERE CAST(sale_date AS DATE) = '2024-01-01';

Use this when users supply parameters that may differ in type from the database column.

Avoiding Errors With CAST

Casting helps prevent type mismatch errors during operations like joins or math calculations. For example, if you try to subtract an INT from a VARCHAR, your query might fail.

Use CAST proactively when combining data across mixed sources, especially in enterprise systems with legacy or user-generated input.

Use the SQL CAST function when you need to safely and explicitly convert between data types. Whether you're aligning types across datasets, formatting for display, or preparing for calculations, CAST gives you the control to make your query results predictable and correct. It’s one of the simplest yet most valuable tools in SQL for keeping your data consistent and clean.