Pierce Technical
SQL lessons20 of 38

Converting types with CAST

Turn text into numbers, numbers into text, and know what each database does when a value won't convert.

Intermediate8 min readLesson 20 of 38
In this lesson 7 sections
  1. Why you’d convert
  2. Cutting off vs. rounding
  3. When a value won’t convert
  4. Dates are their own case
  5. Common mistakes
  6. Dialect notes
  7. Snowflake notes
Jump to practice

Every value has a data type (lesson 9). CAST converts a value from one type to another:

CAST(value AS type)
SELECT CAST('42' AS INTEGER),     -- text '42' becomes the number 42
       CAST(42 AS TEXT),          -- the number 42 becomes the text '42'
       CAST(3.99 AS INTEGER);     -- 3: the decimals are cut off, not rounded

Why you’d convert

Problem Symptom Fix
Numbers stored as text Sorts as 1, 10, 2, 20, and '10' < '9' is TRUE CAST(code AS INTEGER) before comparing or sorting
Whole-number division 7 / 2 is 3 on several databases Make one side a decimal: CAST(7 AS REAL) / 2 is 3.5
Joining mismatched keys An ID is a number in one table and text in another, so nothing matches CAST one side so both are the same type
Building a label Gluing a number to text `‘Deal ’

Here’s the first one in action:

SELECT '10' < '9',                                   -- 1 (TRUE): compared as text
       CAST('10' AS INTEGER) < CAST('9' AS INTEGER); -- 0 (FALSE): compared as numbers

Cutting off vs. rounding

Casting a decimal to a whole number cuts off the decimals. It doesn’t round:

SELECT stage,
       AVG(amount)                  AS exact_avg,
       CAST(AVG(amount) AS INTEGER) AS cut_off,
       ROUND(AVG(amount))           AS rounded
FROM opportunities
WHERE stage = 'Closed Won';
stage exact_avg cut_off rounded
Closed Won 40021.739… 40021 40022

Neither is wrong. Pick on purpose: “whole dollars, ignore the cents” is CAST, “nearest dollar” is ROUND. Databases don’t agree here: SQLite and SQL Server cut off, while PostgreSQL and MySQL round. If the difference matters, use ROUND or a truncate function so the result doesn’t depend on the database.

When a value won’t convert

CAST('abc' AS INTEGER) has no sensible answer. Databases disagree about what to do:

Database CAST('abc' AS INTEGER)
SQL Server, PostgreSQL Error. The whole query stops.
MySQL 0, with a warning
SQLite (the sandbox) 0, silently. CAST('12abc' AS INTEGER) is 12.

SQLite’s silent answer is the dangerous one: bad data turns into real-looking zeros, and nothing warns you. When converting messy data on any database, check first which values won’t convert.

Dates are their own case

Dates in this sandbox are stored as text like '2026-10-01'. That’s fine: the next lesson’s date functions read that text directly. Don’t CAST it:

SELECT CAST('2026-10-01' AS DATE);   -- SQLite returns 2026, not a date

SQLite has no real date type, so it treats DATE as a number and keeps only the leading 2026. On other databases, CAST('2026-10-01' AS DATE) works as you’d expect.

Common mistakes

Mistake Fix
Casting to a whole number when you meant to round ROUND(x), or ROUND(x, 2) for cents
Converting a column inside WHERE on a huge table Store the column as the right type when the data is loaded. Converting every row on every query is slow.
Trusting a cast of messy text Find the values that won’t convert before you trust the result

Dialect notes

Database Notes
SQL Server Also CONVERT(INT, value). Casting a decimal to INT cuts off, like SQLite. TRY_CAST(value AS INT) returns NULL instead of an error.
PostgreSQL Shorthand value::INTEGER. Casting 3.99 to an integer gives 4 (it rounds).
MySQL Whole numbers cast to SIGNED (or UNSIGNED), decimals to DECIMAL(10,2). Casting 3.99 to SIGNED gives 4 (it rounds). TRUNCATE(3.99, 0) cuts off instead.

Snowflake notes

Syntax What it does
value::NUMBER Shorthand for CAST(value AS NUMBER)
TRY_CAST(value AS NUMBER) Returns NULL instead of an error when a value won’t convert
TRY_TO_NUMBER, TRY_TO_DATE Same idea, with format options for messy dates and numbers

CAST('abc' AS NUMBER) stops with an error, like SQL Server. TRY_CAST is how you find bad values when loading data: WHERE TRY_CAST(raw_amount AS NUMBER) IS NULL AND raw_amount IS NOT NULL lists every row that won’t convert. Snowflake’s INTEGER is NUMBER(38,0), and casting 3.99 to it gives 4 (it rounds). Use TRUNC(x) to cut off instead.

More in the field guide: CAST / ::, Integer division and Numbers: precision and scale.

Try it

What’s the average deal amount in each stage, as whole dollars? Cut the cents off rather than rounding. Return the stage and the whole-dollar average, largest first.

Write it in the sandbox below, then press Check answer.

SQL sandbox

Run queries against the sample CRM tables (accounts, opportunities,users, contacts and opportunity_history), right in your browser. Nothing you run leaves your computer.

Back to the top of the lesson