CAST & CONVERT (Type Conversion)

Reviewed & published by Brayan K

Data doesn't always arrive in the type you want. A price imported as text, a date stored as a string, a number that needs to become a label — type conversion is how you reshape a value so SQL treats it correctly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

This lesson covers the portable CAST function, Postgres' :: shorthand, SQL Server's CONVERT, the dangers of implicit conversion, and how to convert dirty data safely without crashing your query.

What You'll Learn

• Convert types with the standard CAST function
• Use the Postgres :: shorthand
• Use SQL Server's CONVERT and style codes
• Turn text into numbers and dates (and back)
• Avoid the traps of implicit conversion
• Guard casts so bad data becomes NULL, not an error

Our Sample Table: invoices

A common mess: amount_text was imported as TEXT even though it holds numbers. We'll cast it back to a real DECIMAL.

1. The CAST Function

The standard form is CAST(value AS type). It produces a new value of the target type for this query only — the underlying column is untouched. It works identically in every major database, which makes it the safe default.

A cast is like a money changer: you hand over a value of one "currency" (text) and get back the same worth in another (a number). The original note isn't destroyed — you just get a usable form.

-- The tables this lesson uses. Every later block queries them, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
-- amount_text is deliberately TEXT: this lesson is about the casts you need
-- when a column arrives as the wrong type, which is what an import usually does.
CREATE TABLE invoices (
    invoice_id  INTEGER PRIMARY KEY,
    amount_text TEXT
);
INSERT INTO invoices (invoice_id, amount_text) VALUES
    (1, '120.50'),
    (2, '85.00'),
    (3, '1499.99');

-- score holds 'N/A' for a game nobody finished, which is exactly the row that
-- makes an unguarded CAST fail.
CREATE TABLE games (
    game  TEXT,
    score TEXT
);
INSERT INTO games (game, score) VALUES
    ('chess',    '12'),
    ('draughts', '7'),
    ('go',       'N/A');

-- CAST(value AS type) changes a value's data type for this query.
SELECT
    CAST('2026-06-17' AS DATE)  AS as_date,
    CAST(42 AS DECIMAL(6,2))    AS as_decimal,
    CAST(19.99 AS INTEGER)      AS as_int;   -- truncates toward zero

-- CAST is ANSI standard and works in every major database.

-- ✅ Expected result:
-- as_date | as_decimal | as_int
-- 2026 | 42 | 19

2. Postgres Shorthand: ::

PostgreSQL lets you write value::type as a compact alternative to CAST. It's handy and readable, but it only works in Postgres — reach for CAST when portability matters.

-- Postgres has a shorthand:  value :: type
SELECT
    '100'::INTEGER      AS num,
    '3.14'::NUMERIC     AS pi,
    '2026-01-01'::DATE  AS new_year;

-- The :: operator is Postgres-only; CAST(...) is the portable form.

-- ✅ Expected result:
-- num | pi | new_year
-- 100 | 3.14 | 2026-01-01

3. SQL Server's CONVERT

CONVERT(type, value, style) is SQL Server's own conversion function. Its extra style argument controls date formatting (e.g. style 23 gives ISO yyyy-mm-dd). Outside SQL Server, stick with CAST.

-- SQL Server uses CONVERT, which adds an optional "style" for dates.
-- CONVERT(target_type, value, style)

SELECT
    CONVERT(VARCHAR, GETDATE(), 23) AS iso_date,   -- 23 = yyyy-mm-dd
    CONVERT(INT, '250')             AS as_int,
    CONVERT(DECIMAL(5,2), 7)        AS as_dec;

-- Result (SQL Server):
-- iso_date   | as_int | as_dec
-- 2026-06-17 | 250    | 7.00
--
-- CONVERT is SQL Server-specific. Prefer CAST unless you need a style code.

4. The Big One: Text → Number

By far the most common cast in practice is fixing a numeric column that was imported as text. Once cast, you can do arithmetic, compare, and aggregate it like any number.

-- The most common real-world cast: text <-> number.
-- Our "amount" column was imported as TEXT by mistake.

SELECT
    invoice_id,
    amount_text,
    CAST(amount_text AS DECIMAL(8,2)) AS amount_num,
    CAST(amount_text AS DECIMAL(8,2)) * 1.1 AS with_tax
FROM invoices;

-- Result:
-- invoice_id | amount_text | amount_num | with_tax
-- 1          | '49.99'     | 49.99      | 54.989
-- 2          | '120.00'    | 120.00     | 132.000
-- 3          | '8.50'      | 8.50       | 9.350
--
-- Without the cast, "amount_text" * 1.1 errors or concatenates.

Your Turn: filter on a casted value

Fill in the target type so the text rating becomes a real number you can compare.

-- 🎯 YOUR TURN — fill in the blanks
-- Goal: turn the text rating into a real number so you can compare it.

SELECT
    product,
    CAST(rating_text AS ____) AS rating_num   -- 👉 type = DECIMAL(3,1)
FROM reviews
WHERE CAST(rating_text AS ____) >= 4.0;        -- 👉 same type again

-- ✅ Expected: only rows whose numeric rating is 4.0 or higher.

5. Implicit vs Explicit Conversion

Some engines silently convert types for you (implicit conversion). It feels convenient but it's a trap: the behaviour differs across databases and hides bugs. Always be explicit with CAST so your intent is unmistakable.

-- IMPLICIT conversion: SQL sometimes converts for you.
SELECT 5 + '3'   AS maybe_8;   -- in MySQL: 8 ; in Postgres: ERROR

-- EXPLICIT is safer and portable — say exactly what you mean:
SELECT 5 + CAST('3' AS INTEGER) AS definitely_8;

-- 8
--
-- Rule of thumb: never rely on implicit conversion. Be explicit.

-- ✅ Expected result:
-- definitely_8
-- 8

6. Casting Dirty Data Safely

A cast must succeed for every row. One stray 'N/A' in a numeric column will crash the whole query. Guard the cast with a CASE (or SQL Server's TRY_CAST) so invalid values become NULL instead of an error.

-- Casting bad data can blow up the whole query.
-- 'N/A' is not a number, so this fails:
SELECT CAST(score AS INTEGER) FROM games;  -- ERROR on 'N/A'

-- Safer: convert only valid values, NULL the rest.
-- Postgres / standard pattern with a CASE guard:
SELECT
    game,
    CASE WHEN score ~ '^[0-9]+$'
         THEN CAST(score AS INTEGER)
         ELSE NULL END AS score_num
FROM games;

-- Result:
-- game   | score_num
-- Chess  | 12
-- Go     | (null)     <- was 'N/A'
-- Cards  | 7

Reorder Challenge

These pieces of a single CAST expression are scrambled. Reassemble them in order:

A.   AS DECIMAL(8,2)
B.   CAST(
C.   )
D.   amount_text

Quick Recall

1) What does CAST(19.99 AS INTEGER) return?

19 — casting a decimal to an integer truncates toward zero; it does not round.

2) Which conversion form is portable across all major databases?

CAST(value AS type). The :: shorthand is Postgres-only and CONVERT is SQL Server-specific.

3) Your cast errors because one row holds 'N/A'. What's the fix?

Guard it with a CASE (or TRY_CAST) so only valid values are cast and the rest become NULL instead of crashing the query.

Common Errors (and the fix)

• "invalid input syntax for integer": a non-numeric value snuck into the column. Guard with CASE or TRY_CAST.
• Using :: outside Postgres: MySQL and SQL Server don't understand it. Use CAST.
• Expecting rounding: CAST to an integer truncates. Call ROUND() first if you want nearest.
• Losing precision: casting to DECIMAL(5,2) when the value has 4 decimals silently drops digits. Size the type to fit.
• Relying on implicit conversion: 5 + '3' works in MySQL but errors in Postgres. Be explicit.

Mini-Challenge: Fix the Signups Table

Cast a text date and a text age into real types, then do maths on them. Write it and verify in a playground.

-- 🎯 MINI-CHALLENGE
-- The "signups" table stored joined_on as TEXT (e.g. '2026-03-01').
--   1. Cast joined_on to a DATE
--   2. Cast the text column "age" to an INTEGER
--   3. Return name, the real date, and age + 1 as "next_birthday_age"
--
-- ✅ Expected: name, joined_date (a real DATE), next_birthday_age (a number)

-- your query here

🎉 Lesson Complete

• ✅ CAST(v AS type) is the portable, standard conversion
• ✅ :: (Postgres) and CONVERT (SQL Server) are engine-specific
• ✅ Casting decimal → integer truncates; ROUND first if needed
• ✅ Always be explicit — never rely on implicit conversion
• ✅ Guard casts so dirty data becomes NULL, not an error
• ✅ Next: the HAVING clause — filtering grouped results

Practice quiz

Continue this course

• Previous: Date & Time Functions
• Next: UNION, INTERSECT & EXCEPT — Combine the results of multiple queries
• Quick reference: SQL cheat sheet

Frequently asked questions

What's the difference between CAST and CONVERT?

CAST(value AS type) is ANSI standard and works in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. CONVERT is mostly a SQL Server function that adds an optional style code for formatting dates. For portable code, always use CAST unless you specifically need CONVERT's style argument.

What happens when I cast a decimal to an integer?

Most engines truncate toward zero rather than rounding: CAST(19.99 AS INTEGER) gives 19, and CAST(-2.7 AS INTEGER) gives -2. If you want rounding, call ROUND() first, then cast — or cast to a decimal with the precision you need.

Why does casting bad text to a number error out?

A cast must succeed for every row it touches. If even one value like 'N/A' or 'twelve' can't be parsed, the whole query fails. Guard the cast with a CASE or a regex check (or TRY_CAST in SQL Server) so invalid values become NULL instead of crashing the query.

Is implicit conversion ever okay?

It's convenient but unpredictable — MySQL happily computes 5 + '3' = 8, while PostgreSQL raises an error. Relying on it makes queries non-portable and hides bugs. Always convert explicitly with CAST so your intent is clear and the behaviour is consistent.