Type Conversion (CAST & CONVERT)

Data does not always arrive in the type you need it in. A number might come in as text, a date might be stored as a string, or you might need to turn a numeric ID into text to concatenate it into a label. SQL gives you explicit functions to convert a value from one type to another, and understanding when conversion happens automatically versus when you must ask for it explicitly is important for both correctness and performance.

CAST — the ANSI-standard converter

CAST(expression AS type) is defined by the SQL standard and works, with the same syntax, on virtually every relational database. It should be your default choice whenever portability matters.

CAST examples

SQL

SELECT CAST('123' AS INTEGER);           -- 123 (text to integer)
SELECT CAST(order_total AS VARCHAR(20));  -- number to text
SELECT CAST('2026-07-08' AS DATE);        -- text to date

Dialect alternatives

Syntax	Database	Example
`CAST(expr AS type)`	ANSI standard — all major databases	`CAST('42' AS INTEGER)`
`CONVERT(type, expr, style)`	SQL Server	`CONVERT(VARCHAR, order_date, 23)`
`expr::type`	PostgreSQL shorthand	`'42'::INTEGER`

SQL Server's CONVERT

SQL Server's CONVERT() takes an extra style argument that controls date/number formatting (something CAST() cannot do), which is why CONVERT() is still commonly used on SQL Server even though CAST() is also supported there.

CONVERT vs CAST vs :: shorthand

SQL

-- SQL Server: CONVERT with a style code for date formatting
SELECT CONVERT(VARCHAR, order_date, 23) AS order_date_text; -- 'YYYY-MM-DD'

-- PostgreSQL: :: shorthand cast
SELECT '42'::INTEGER;
SELECT order_total::TEXT;

Implicit vs. explicit conversion

Many databases will automatically convert values for you in certain contexts — comparing a numeric column to a string literal, for example, might silently convert one side so the comparison can proceed. This is called implicit conversion, and while convenient, it is a common source of subtle bugs.

Be careful relying on implicit conversion

Implicit conversion rules differ by database, and even within one database they can quietly defeat performance. Comparing an indexed numeric or date column against a string literal, for instance, can force the database to convert every row's value before comparing — which often disables use of an index on that column and turns a fast lookup into a full table scan. When correctness or performance matters, convert explicitly with CAST() rather than hoping the database converts the way you expect.

Worked example

Converting a string to a number and a date explicitly

SQL

SELECT
  CAST(discount_code AS INTEGER) AS discount_id,
  CAST(signup_date_text AS DATE) AS signup_date
FROM promotions;