CASTFunctions
Buy Me A Coffee

Convert a value from one data type to another.

Explicitly converts a value from one data type to another, failing at runtime if the conversion is not possible. Preferred over implicit conversion for clarity and to avoid engine-specific coercion behaviors. Equivalent to double-colon syntax in PostgreSQL.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all CAST(expr AS type). Also CONVERT(expr, type). Supports a limited set of target types (CHAR, SIGNED, UNSIGNED, DATE, DATETIME, DECIMAL, JSON, BINARY).
PostgreSQL ✓ Supported all CAST(expr AS type). Also :: shorthand cast operator (expr::type) which is very commonly used in practice and works anywhere an expression is valid.
SQL Server ✓ Supported all CAST(expr AS type). Also CONVERT(type, expr [, style]) which adds optional style codes for date/number formatting.
Oracle ✓ Supported all CAST standard. TO_DATE(), TO_NUMBER(), TO_CHAR() are the traditional Oracle conversion functions for common types.
SQLite ✓ Supported all CAST(expr AS type). SQLite uses flexible type affinity; CAST influences storage affinity rather than strictly enforcing types.

Details

CAST is SQL-standard. PostgreSQL's :: shorthand is unique and extremely common in practice. SQL Server's CONVERT adds format-style codes useful for date/number display.

Standard Syntax

SELECT CAST(price AS VARCHAR(20)) FROM products;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

CAST(expr AS type). Also CONVERT(expr, type). Supports a limited set of target types (CHAR, SIGNED, UNSIGNED, DATE, DATETIME, DECIMAL, JSON, BINARY).

SELECT CAST(price AS CHAR) FROM tbl; SELECT CONVERT(price, CHAR) FROM tbl;

PostgreSQL

CAST(expr AS type). Also :: shorthand cast operator (expr::type) which is very commonly used in practice and works anywhere an expression is valid.

SELECT CAST(price AS TEXT) FROM tbl; SELECT price::text FROM tbl; -- :: shorthand SELECT '2024-01-01'::date; SELECT '42'::int + 1;

SQL Server

CAST(expr AS type). Also CONVERT(type, expr [, style]) which adds optional style codes for date/number formatting.

SELECT CAST(price AS NVARCHAR(20)) FROM tbl; SELECT CONVERT(VARCHAR, GETDATE(), 103); -- style 103 = dd/mm/yyyy

Oracle

CAST standard. TO_DATE(), TO_NUMBER(), TO_CHAR() are the traditional Oracle conversion functions for common types.

SELECT CAST(price AS VARCHAR2(20)) FROM tbl; SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM tbl; SELECT TO_NUMBER('42.5') FROM DUAL;

SQLite

CAST(expr AS type). SQLite uses flexible type affinity; CAST influences storage affinity rather than strictly enforcing types.

SELECT CAST(price AS TEXT) FROM tbl; SELECT CAST('3.14' AS REAL);