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.

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

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.

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.

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 .

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.

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

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.

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.

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

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.

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

Practice quiz

What is the standard, portable syntax for type conversion in SQL?

  • CONVERT(value, type)
  • value::type
  • CAST(value AS type)
  • TO_TYPE(value)

Answer: CAST(value AS type). CAST(value AS type) is ANSI standard and works in every major database.

What does CAST(19.99 AS INTEGER) return?

  • 19
  • 20
  • 19.99
  • An error

Answer: 19. Casting a decimal to an integer truncates toward zero, so 19.99 becomes 19, not 20.

Does CAST change the underlying column in the table?

  • Yes, permanently
  • Only for numbers
  • It deletes the column
  • No — it produces a new value of the target type for this query only

Answer: No — it produces a new value of the target type for this query only. CAST produces a converted value for the query; the stored column is untouched.

Which cast shorthand is PostgreSQL-only?

  • CAST(v AS type)
  • v::type
  • CONVERT(type, v)
  • TO_NUMBER(v)

Answer: v::type. The :: operator is Postgres-specific; CAST is the portable form.

Which function is SQL Server-specific and adds a date 'style' argument?

  • CONVERT
  • CAST
  • ::
  • PARSE_DATE

Answer: CONVERT. CONVERT(type, value, style) is SQL Server's conversion function with an optional style code.

Why is relying on implicit conversion like 5 + '3' risky?

  • It is always slower
  • It changes the table
  • Behaviour differs across engines — MySQL gives 8, Postgres errors
  • It only works on dates

Answer: Behaviour differs across engines — MySQL gives 8, Postgres errors. Implicit conversion is non-portable and hides bugs; be explicit with CAST instead.

What happens if a single value like 'N/A' can't be cast to a number?

  • That row is skipped
  • The whole query fails with an error
  • It becomes 0
  • It becomes NULL automatically

Answer: The whole query fails with an error. A cast must succeed for every row; one unparseable value crashes the entire query.

How can you safely cast a column that may contain invalid values?

  • Ignore the errors
  • Cast twice
  • Use a WHERE clause only
  • Guard the cast with a CASE (or TRY_CAST) so bad values become NULL

Answer: Guard the cast with a CASE (or TRY_CAST) so bad values become NULL. A CASE guard or SQL Server's TRY_CAST turns invalid values into NULL instead of erroring.

Why is casting a numeric column imported as TEXT important before arithmetic?

  • It looks nicer
  • Without the cast, math on text errors or concatenates instead of computing
  • Text is faster
  • It is never needed

Answer: Without the cast, math on text errors or concatenates instead of computing. Once cast to a number you can do arithmetic, compare, and aggregate; text would error or concatenate.

If you want a decimal rounded (not truncated) before casting to an integer, what should you do?

  • Nothing — CAST rounds
  • Use :: instead
  • Call ROUND() first, then cast
  • Cast twice

Answer: Call ROUND() first, then cast. CAST to integer truncates; call ROUND() first if you want the nearest whole number.