Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Operators and Predicates

SQL CAST function

Reference for SQL CAST(expr AS type): convert between text, integers, decimals and dates, how SQLite casts leniently, failure behavior and dialect shortcuts.

CAST(expression AS type) converts a value to another data type. It is standard SQL and the portable way to turn text into numbers, numbers into text, or decimals into integers. Use it when an operation needs a particular type: dividing two integers as decimals, sorting numbers stored as text, or joining on columns of different types.

Databases differ in how strict a cast is. PostgreSQL and SQL Server raise an error for CAST('abc' AS INTEGER); SQLite quietly returns 0.

SQL
SELECT CAST('42' AS INTEGER) + 1;

Try it

SQL Casting between types

Press Run (or Ctrl + Enter) to see the result here.

SQL SQLite casts leniently

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CAST(expression AS data_type)
Part Description
expression The value to convert.
data_type The target type: INTEGER, DECIMAL(10, 2), VARCHAR(20), DATE, TEXT, ... Available types depend on the database.

Returns: the converted value; CAST(NULL AS type) is NULL. A value that cannot be converted raises an error in most databases.

SQLite maps the target type name to one of five storage classes by its affinity rules: INTEGER, REAL, TEXT, BLOB or NUMERIC. Names such as VARCHAR(20) become TEXT and the length is ignored.

Dialect differences

Behavior SQLite PostgreSQL MySQL SQL Server Oracle
CAST('abc' AS INTEGER) 0 Error 0 with a warning Error Error
CAST('12abc' AS INTEGER) 12 Error 12 with a warning Error Error
Real to integer Truncates toward zero Rounds Rounds Truncates Rounds
Shortcut syntax None value::type CONVERT(value, type) CONVERT(type, value) TO_NUMBER(), TO_CHAR(), TO_DATE()
Safe variant None needed None (use a regex check) None TRY_CAST() CAST(... DEFAULT x ON CONVERSION ERROR AS ...) (12.2+)
CAST(... AS DATE) No date type: stays text Yes Yes Yes Yes
SQL
-- PostgreSQL
SELECT '42'::integer, 7.5::integer;   -- 42, 8

-- SQL Server
SELECT TRY_CAST('abc' AS INT);        -- NULL instead of an error
SELECT CONVERT(VARCHAR(10), GETDATE(), 23);

-- Oracle
SELECT TO_NUMBER('42') FROM dual;

Usage notes

  • Cast the right side, not the column. WHERE CAST(id_text AS INTEGER) = 5 cannot use an index on id_text. Compare id_text = '5' or store the right type to begin with. See sargable predicates.
  • Integer division fix: CAST(a AS REAL) / b (or DECIMAL) avoids truncating. See arithmetic operators.
  • Rounding differs. Casting 7.9 to an integer truncates in SQLite and SQL Server, but rounds in PostgreSQL, MySQL and Oracle. Call ROUND(), CEIL() and FLOOR() first when you care.
  • MySQL type names: CAST accepts only a few targets there (SIGNED, UNSIGNED, DECIMAL(p, s), CHAR, DATE, DATETIME, ...), so write CAST(x AS SIGNED) instead of AS INTEGER.
  • Text length: CAST(x AS VARCHAR(5)) truncates in some databases and errors in others.
  • Dates: CAST(text AS DATE) expects the ISO format YYYY-MM-DD in most engines. SQLite has no date type, so the result stays text; use DATE().
  • Implicit conversion happens in comparisons and arithmetic, but rules differ. An explicit CAST documents your intent and behaves predictably.
  • Check with TYPEOF() in SQLite to see which storage class a value ended up in.
  • Booleans: CAST('true' AS BOOLEAN) works in PostgreSQL but not in SQLite, which has no boolean type.