CAST & CONVERT (Type Conversion)

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.

Learn CAST & CONVERT (Type Conversion) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick…

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

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.

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.

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 .

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.

Your Turn: filter on a casted value

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

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.

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.

Reorder Challenge

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

Quick Recall

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.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

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.

🎉 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: The HAVING Clause