SQL CAST Explained: Convert Data Types With Tested Examples
SQL CAST converts a value from one data type to another: text to number, number to text, decimal to integer, string to date. Learn the syntax, what really happens to your data, the integer division and sorting bugs CAST fixes, and how CAST differs across databases.
- SQL CAST uses
CAST(expression AS type)and is part of standard SQL, so it works in every major database. - Casting a decimal to an integer truncates toward zero: 5.95 becomes 5 and -5.95 becomes -5. Use
ROUNDwhen you want rounding. - Numbers stored as text sort and compare as text:
'10'sorts before'9'. CAST fixes it. - Bad input behaves differently by database: SQLite quietly returns 0, while SQL Server and PostgreSQL raise errors.
Data rarely arrives in the type you need. Prices come from a CSV as text, IDs need to be glued into labels, and an average of two integers mysteriously loses its decimals. SQL CAST is the standard way to convert between types, and understanding exactly what it does to your values prevents a whole family of quiet reporting bugs.
All queries were run on SQLite 3.50.4, and the output shown is from those runs. SQLite is very forgiving about types, so we also explain where SQL Server, PostgreSQL, MySQL and Oracle behave differently. Those notes come from their documentation, not from runs on our side.
What is CAST in SQL?
CAST is a function-like expression that takes a value and returns it as a different data type. It does not change the stored column; it changes the value used in that one query. You can use it in SELECT, WHERE, ORDER BY, JOIN conditions and anywhere else an expression is allowed.
Typical reasons to cast: doing math on numbers stored as text, getting decimal results from integer columns, joining columns of different types, formatting output, and loading imported data into properly typed tables.
SQL CAST syntax
CAST(expression AS target_type)
-- examples
CAST('42' AS INTEGER)
CAST(price AS DECIMAL(10, 2))
CAST(order_id AS VARCHAR(20))
The target type must be a type your database understands. Common ones are INTEGER, DECIMAL(p, s) or NUMERIC, REAL or FLOAT, VARCHAR(n) or TEXT, DATE and TIMESTAMP.
Basic conversions
SELECT CAST('42' AS INTEGER) AS str_to_int,
CAST(42 AS TEXT) || '!' AS int_to_text,
CAST('3.14' AS REAL) AS str_to_real;
-- str_to_int | int_to_text | str_to_real
-- 42 | 42! | 3.14
Text to number and number to text are the conversions you will write most often. The || operator concatenates strings in SQLite, PostgreSQL and Oracle; SQL Server uses + or CONCAT(), and MySQL uses CONCAT().
Decimal to integer: truncation, not rounding
SELECT CAST(5.95 AS INTEGER) AS trunc,
CAST(-5.95 AS INTEGER) AS neg_trunc,
CAST(2.5 AS INTEGER) AS cast_25,
ROUND(2.5) AS round_25,
ROUND(2.567, 2) AS round2;
-- trunc | neg_trunc | cast_25 | round_25 | round2
-- 5 | -5 | 2 | 3.0 | 2.57
CAST to an integer simply drops the fractional part, moving toward zero. 5.95 becomes 5, not 6, and -5.95 becomes -5, not -6. If a report should show rounded values, use ROUND first, and cast afterwards only if you need an integer type. This matches SQL Server, which also truncates when casting numeric values to INT. PostgreSQL rounds instead when casting a numeric to an integer, another reason to be explicit with ROUND or TRUNC.
Fixing integer division
SELECT 7/2 AS int_div,
CAST(7 AS REAL)/2 AS real_div,
7*1.0/2 AS times_one;
-- int_div | real_div | times_one
-- 3 | 3.5 | 3.5
When both operands are integers, many databases (SQLite, SQL Server and PostgreSQL among them) perform integer division and throw away the remainder. That is how a conversion rate of 1 order per 3 visits turns into 0. Cast one side to a decimal or real type first. Multiplying by 1.0 works too, but CAST states the intent clearly. MySQL is an exception: its / operator always returns a decimal.
Tip: cast before dividing, not after. CAST(7/2 AS REAL) gives 3.0, because the integer division already happened.
Numbers stored as text: the sorting bug
CREATE TABLE prices (sku TEXT, price TEXT);
INSERT INTO prices VALUES ('A','9'), ('B','10'), ('C','100'), ('D','25.5');
SELECT sku, price FROM prices ORDER BY price;
-- B 10 | C 100 | D 25.5 | A 9
SELECT sku, price FROM prices ORDER BY CAST(price AS REAL);
-- A 9 | B 10 | D 25.5 | C 100
Text is compared character by character, so '10' comes before '9' because '1' is less than '9'. Casting in the ORDER BY gives the numeric order users expect. The real fix is to store numbers in numeric columns, but CAST gets you correct results from messy imports today. More on sorting rules in our SQL ORDER BY guide.
Text comparisons in WHERE
SELECT COUNT(*) FROM prices WHERE price > '9'; -- 0
SELECT COUNT(*) FROM prices WHERE CAST(price AS REAL) > 9; -- 3
The first query asks which strings sort after '9', and none do. The second asks which prices are greater than nine, and three are. Filters on text-typed numbers are a classic source of reports that are silently empty. Always compare numbers to numbers.
Converting numbers to text
SELECT 'Order #' || CAST(1001 AS TEXT) AS label,
'Total: ' || CAST(ROUND(19.999, 2) AS TEXT) AS total;
-- label | total
-- Order #1001 | Total: 20.0
Notice the total: ROUND produced the float 20.0, and CAST kept the trailing .0 rather than 20.00. CAST converts types; it does not format. For currency or fixed decimals, use a formatting function such as printf('%.2f', x) in SQLite, FORMAT() in SQL Server and MySQL, or TO_CHAR() in PostgreSQL and Oracle. Our SQL string functions guide lists more.
Converting strings to dates
SELECT CAST('2026-11-19' AS DATE) AS date_cast,
typeof(CAST('2026-11-19' AS DATE)) AS t;
-- date_cast | t
-- 2026 | integer
SELECT date('2026-11-19') AS d,
julianday('2026-11-19') - julianday('2026-11-01') AS days;
-- d | days
-- 2026-11-19 | 18.0
This is the biggest SQLite surprise. SQLite has no real DATE type, so CAST(... AS DATE) applies numeric rules and keeps only the leading 2026. Use SQLite date functions instead. In SQL Server, PostgreSQL, MySQL and Oracle, CAST('2026-11-19' AS DATE) does return a real date when the string is in ISO format (YYYY-MM-DD). Other formats, such as 19/11/2026, depend on server settings, so prefer ISO strings or explicit parsing functions like TO_DATE or CONVERT with a style code. See our SQL date functions guide.
What happens with bad input?
SELECT CAST('12abc' AS INTEGER) AS prefix,
CAST('abc' AS INTEGER) AS junk,
CAST('' AS INTEGER) AS empty,
CAST(NULL AS INTEGER) AS null_in;
-- prefix | junk | empty | null_in
-- 12 | 0 | 0 | NULL
SQLite never fails a CAST. It reads the longest numeric prefix and returns 0 when there is none, so bad data turns into plausible-looking zeros. Casting NULL returns NULL in every database. Other engines are stricter: SQL Server raises a conversion error, and offers TRY_CAST, which returns NULL instead; PostgreSQL raises an invalid input error. Before casting imported data in SQLite, validate it, for example with WHERE price GLOB '[0-9]*', or count zeros that should not exist. Pair CAST with COALESCE to supply defaults for NULLs.
CAST across databases
| Database | Shorthand | Safe version | Notes |
|---|---|---|---|
| SQLite | none | not needed, never errors | No DATE type; bad text becomes 0 |
| PostgreSQL | value::type |
none built in | Strict; errors on bad input |
| SQL Server | none | TRY_CAST, TRY_CONVERT |
CONVERT adds date styles |
| MySQL | none | none | Uses SIGNED, UNSIGNED, CHAR as targets |
| Oracle | none | DEFAULT ... ON CONVERSION ERROR |
TO_NUMBER, TO_DATE common |
Only the SQLite row was run here. The others summarise each vendor’s documentation, so test against your own version.
CAST vs CONVERT
CAST is ANSI standard and portable. CONVERT is a SQL Server and MySQL extension. In SQL Server, CONVERT(VARCHAR(10), order_date, 103) also takes a style code that formats dates, which CAST cannot do. In MySQL, CONVERT(expr, type) works like CAST, and CONVERT(expr USING utf8mb4) changes character sets. Use CAST by default and CONVERT only when you need those extras.
CAST and indexes
Wrapping a column in CAST inside WHERE or JOIN, such as WHERE CAST(order_id AS TEXT) = '1001', usually stops the database from using a plain index on that column, because the index stores the original values. Cast the constant instead (WHERE order_id = CAST('1001' AS INTEGER)), fix mismatched column types, or create an expression index if your database supports one.
Common SQL CAST mistakes
- Expecting CAST to round. It truncates in SQLite and SQL Server; use ROUND.
- Casting after integer division instead of before.
- Using CAST for display formatting instead of a format function.
- Trusting
CAST(... AS DATE)in SQLite. - Letting bad text become 0 silently in SQLite or crash a whole query elsewhere; validate first or use TRY_CAST.
- Casting indexed columns in filters and wondering why the query got slow.
Final thoughts
SQL CAST is a small expression with big consequences. Use it to make numbers behave like numbers, to keep decimals in division and to convert types at system boundaries. Remember that it truncates, that it does not format, and that each database handles bad input differently. Above all, store values in the right types from the start, so that CAST becomes the exception rather than the rule.
Frequently asked questions
Does CAST round or truncate in SQL?
Casting a decimal to an integer truncates toward zero in SQLite and SQL Server, so 5.95 becomes 5. PostgreSQL rounds instead. Use ROUND or TRUNC to make the result explicit.
How do I convert a string to a number in SQL?
Use CAST(column AS INTEGER) or CAST(column AS DECIMAL(10, 2)). Validate the text first, because invalid values raise errors in most databases and become 0 in SQLite.
Why does my SQL division return 0?
Both operands are integers, so the database performs integer division and drops the remainder. Cast one operand to a decimal or real type before dividing.
What is the difference between CAST and CONVERT?
CAST is standard SQL and works everywhere. CONVERT exists in SQL Server and MySQL and adds extras, such as date style codes in SQL Server and character set conversion in MySQL.
What is TRY_CAST?
TRY_CAST is a SQL Server function, also found in some other engines, that returns NULL instead of raising an error when a value cannot be converted to the target type.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.