CAST & Type Conversion

Every value in SQL has a type — integer, decimal, text, date — and operations depend on it: '10' < '9' is true for text but false for numbers. CAST converts a value from one type to another, and knowing when databases convert automatically saves you from subtle bugs.

CAST

SELECT CAST('42' AS INTEGER) + 1 AS number,
       CAST(42 AS TEXT) || '!' AS text,
       CAST(7 AS REAL) / 2 AS decimal,
       CAST(3.99 AS INTEGER) AS truncated;

CAST(value AS type) is standard and works everywhere. Converting a decimal to an integer drops the fraction (it truncates; it doesn't round). PostgreSQL also offers the shorthand value::type.

Why types matter: text vs numbers

SELECT '10' < '9' AS as_text, 10 < 9 AS as_numbers;

Text compares character by character, and '1' comes before '9'. If numbers end up stored as text — common with imported data — sorting and comparisons go wrong until you cast them.

SELECT value FROM (SELECT '100' AS value UNION ALL SELECT '25' UNION ALL SELECT '3')
ORDER BY CAST(value AS INTEGER);

Implicit conversion

SELECT '5' + 3 AS added, 5 || 3 AS joined;

Databases convert values automatically when an operator needs a particular type. How eagerly they do it varies: SQLite and MySQL are relaxed, while PostgreSQL refuses many implicit conversions ('5' + 3 works, but comparing a text column with a number often raises an error). Explicit casts make your intent clear and portable.

SQLite's flexible typing

SELECT typeof(42), typeof(4.2), typeof('42'), typeof(NULL), typeof(x'00');

SQLite has five storage classes: integer, real, text, blob and null. A column's declared type is a preference ("type affinity"), not a strict rule, unless the table is declared STRICT. Other databases enforce column types strictly (lesson 41).

Converting to and from dates

SELECT date('2026-03-05') AS iso_date,
       strftime('%d/%m/%Y', '2026-03-05') AS european,
       date(1767225600, 'unixepoch') AS from_unix_time;

Turning a date into display text is formatting; turning text into a date is parsing. Each database has its own functions: TO_CHAR/TO_DATE (PostgreSQL, Oracle), DATE_FORMAT/STR_TO_DATE (MySQL), FORMAT/CONVERT (SQL Server).

When conversion fails

SELECT CAST('abc' AS INTEGER) AS not_a_number, CAST('12abc' AS INTEGER) AS partial;

SQLite quietly converts what it can (here 0 and 12). Stricter databases raise an error instead, which is safer but can stop a whole query because of one bad row. SQL Server's TRY_CAST and PostgreSQL's input validation functions let you test values first.

Try it yourself

Show each product's price as a whole number of euros and as a whole number of cents.