1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL CAST explained: convert data types with tested examples

Key takeaways

  • 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 ROUND when 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
Software Development

CORS Explained: Preflight Requests, Headers and How to Fix CORS Errors

CORS explained: origins, simple and preflight requests, the response headers your server must send, credentials, and how to fix common…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Databases & SQL

SQL CAST Explained: Convert Data Types With Tested Examples

SQL CAST explained with tested examples: text to number, truncation, integer division, numbers stored as text, dates, bad input and…

Read article →
Databases & SQL

SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)

SQL DISTINCT explained with tested queries: one and many columns, NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, the SUM DISTINCT…

Read article →
Software Development

JavaScript Reduce Explained: 12 Practical Examples

JavaScript reduce explained step by step with 12 practical examples: sums, tallies, grouping, Maps, pipelines, sequential promises and the empty…

Read article →
Software Development

Python Random Module: randint, choice, seed and More

The Python random module explained: randint, randrange, choice, choices, sample, shuffle, seed, weights and when to use secrets instead, with…

Read article →
Software Development

Python Slicing Explained: Start, Stop and Step With Examples

Python slicing explained with start, stop and step, negative indexes, reversing, copying and slice assignment, with tested examples and a…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
Software Development

URL Encoding Explained: Spaces, Plus Signs and UTF-8

URL encoding explained with tested Python and JavaScript code: percent encoding, reserved characters, %20 vs plus, UTF-8 bytes, encodeURI vs…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Software Development

JavaScript Set: Union, Intersection and Removing Duplicates

JavaScript Set explained with tested examples: add, has, delete, deduplicating arrays, the new union and intersection methods, equality rules and…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →