SQL cast: Converting Data Types in Queries

by

Ever tried adding a date to a number in SQL? Or comparing a string to an integer? If you have, chances are you hit an error or got strange results. That’s because SQL is strict about data types. Enter the magical tool: CAST. It lets you convert data from one type to another—safely and smartly.

TL;DR Summary

When working with SQL, sometimes your data types don’t match. That’s where CAST and CONVERT come in. They help change one data type to another so your queries work better. Use them when combining, comparing, or displaying data in different formats. It makes your queries more flexible and powerful.

What Is SQL CAST?

CAST is a SQL function that changes the data type of a value.

Say you have a text value like “123”, but you want it to behave like a number. You can use CAST to turn that string into an integer.

The basic syntax looks like this:


CAST(expression AS target_data_type)

And here’s a quick example:


SELECT CAST('123' AS INT) AS converted_number;

This takes the string ‘123’ and converts it into an integer, so you can do math with it.

Why Would You Use CAST?

Good question! Here are some common reasons:

  • Combine Different Data Types: For example, adding a number to a string version of a number.
  • Format Output: Like turning a float into a string for neat reports.
  • Fix Errors: Prevent type mismatch errors in joins or comparisons.

Let’s see it in real life.


SELECT 'The price is ' + CAST(price AS VARCHAR)
FROM products;

In this case, the number (price) is converted to text so it can be joined with other text.

CAST vs CONVERT

SQL gives you two functions to change data types: CAST and CONVERT.

Both do similar things, but here’s how they differ:

  • CAST is a standard SQL function. It works the same across different database systems.
  • CONVERT is Microsoft SQL Server-specific and gives you more options for formatting dates and numbers.

Here’s an example of CONVERT:


SELECT CONVERT(VARCHAR, GETDATE(), 101) AS formatted_date;

This converts the current date into a MM/DD/YYYY format. Pretty cool, right?

Common Data Type Conversions

Here are the usual suspects when it comes to conversion:

  • String to Integer: CAST(‘789’ AS INT)
  • Integer to String: CAST(789 AS VARCHAR)
  • Date to String: CAST(GETDATE() AS VARCHAR)
  • Float to Integer: CAST(123.45 AS INT)

Be aware: when you convert from float to int, you lose the decimals!


SELECT CAST(123.99 AS INT) AS result;
-- Output: 123 (not 124)

SQL just chops off the decimal. It doesn’t round it. So be careful!

Using CAST in Math

Imagine you’re doing division and both numbers are integers. SQL will return an integer result—no decimals!


SELECT 10 / 4 AS result;
-- Output: 2

Oops! Where’s the 0.5? You’ve lost it because integers were used.

Let’s fix it with CAST:


SELECT CAST(10 AS FLOAT) / 4 AS result;
-- Output: 2.5

Much better.

CAST in WHERE Clauses

You can also use CAST in WHERE clauses to help filter data.


SELECT *
FROM orders
WHERE CAST(order_date AS DATE) = '2024-01-01';

This helps if order_date is actually a datetime and you only care about the date part.

Combining CAST with filters = powerful stuff!

Nested CASTs

Can you convert something, then convert it again? Absolutely.


SELECT CAST(CAST(price AS FLOAT) AS VARCHAR)
FROM products;

This takes a number, turns it into a float, and then into text. It’s a “conversion sandwich”!

When Not to Use CAST

Sometimes, casting is not the best idea. Be cautious when:

  • You Could Lose Data: Converting float to int removes decimals.
  • The Format Doesn’t Match: Converting ‘abc’ to INT will cause an error.
  • Your Database Already Stores Correct Types: Don’t CAST just because!

Always test your CAST before using it in huge queries.

Image not found in postmeta

Bonus: Using TRY_CAST

Some databases (like SQL Server) have TRY_CAST. It’s like CAST, but safer.

Instead of throwing an error, it returns NULL when it fails.


SELECT TRY_CAST('oops' AS INT) AS result;
-- Output: NULL

This is great for cleaning messy data or avoiding query crashes.

Real World Example

Let’s say you have a table of survey responses, and a column is stored as text—like “age” values.

To calculate the average age, you’ll need to turn those text values into numbers:


SELECT AVG(CAST(age AS INT)) AS average_age
FROM survey;

Now you’ve got useful data from a weird column. That’s the power of CAST!

Recap

CAST is your best friend when dealing with mismatched data types in SQL. It helps you:

  • Convert text into numbers (and vice versa)
  • Manipulate dates and floats correctly
  • Avoid frustrating errors with comparisons

Use CAST often, but use it smartly.

Final Tips

  • Start small: Try casting one column before using it everywhere.
  • Check for NULLs: Invalid casts may result in missing data.
  • Use TRY_CAST when you think conversion could fail.

And most importantly—have fun querying!

SQL is cool. CAST makes it cooler.