Conversion Functions

Why You Need Conversion Functions

SQL is strongly typed at the column level, but expressions sometimes need to move a value from one type to another — a numeric ID stored oddly as text, a decimal that needs to display as a formatted string, or a date embedded in a VARCHAR column that needs to be filtered as a real date.

CAST — The Standard, Portable Way

sql
SELECT CAST('123' AS INTEGER) AS as_int;
-- 123   (now a real integer, not text)

SELECT CAST(amount AS VARCHAR(20)) AS amount_text FROM orders WHERE id = 1;
-- '250.00'   (now a string)

SELECT CAST('2024-01-05' AS DATE) AS as_date;
-- 2024-01-05   (now a real DATE value, usable in date arithmetic)

CAST(expression AS target_type) is standard SQL and works identically across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. It's the safest, most portable choice.

CONVERT — The Dialect-Specific Way

SQL Server uses CONVERT() with a different (and reversed) argument order:

sql
SELECT CONVERT(INT, '123') AS as_int;              -- SQL Server: type comes FIRST
SELECT CONVERT(VARCHAR(20), amount) AS amount_text; -- SQL Server

SQL Server's CONVERT() also accepts an optional third "style" argument for formatting (particularly common with dates), which CAST does not support:

sql
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS us_format;  -- SQL Server: 'MM/DD/YYYY' style

MySQL also has a CONVERT() function, but with syntax closer to CAST:

sql
SELECT CONVERT('123', SIGNED) AS as_int;   -- MySQL

Practical Use: Comparing Mismatched Types

sql
-- If a column was mistakenly stored as VARCHAR but holds numeric-looking text
SELECT * FROM orders WHERE CAST(amount AS DECIMAL(10,2)) > 200;

Implicit vs Explicit Conversion

Many engines will implicitly convert compatible types without an explicit CAST — e.g. comparing a numeric column to a numeric-looking string literal, or concatenating a number into a string. Implicit conversion is convenient but can silently produce unexpected results (or errors, or even prevent an index from being used efficiently in some engines) when the two types aren't cleanly compatible. Using explicit CAST documents intent and avoids relying on the engine's guesswork.

Edge Cases

• CAST('abc' AS INTEGER) raises an error in virtually every dialect — text that isn't numeric-looking cannot become a number.
• CAST(NULL AS INTEGER) succeeds and simply returns a typed NULL.
• Converting a DECIMAL to a narrower type (e.g. CAST(316.6666 AS INTEGER)) truncates or rounds depending on the dialect — always verify which behavior your engine uses when precision matters.
• Some dialects offer a "safe" conversion function that returns NULL instead of erroring on bad input (e.g. SQL Server's TRY_CAST() / TRY_CONVERT()), useful for cleaning messy data.

Key Takeaways / Interview Q&A

Q: What's the difference between CAST and CONVERT? A: CAST is standard SQL, portable across dialects, with the syntax CAST(expr AS type). CONVERT is dialect-specific (notably SQL Server and MySQL) with different, engine-specific argument orders and often extra formatting options.

Q: Which one supports a "style" argument for date formatting? A: SQL Server's CONVERT() — CAST does not support a style/format argument.

Q: What happens with CAST('abc' AS INTEGER)? A: It raises a conversion error, since 'abc' isn't a valid numeric string.

Q: How can you convert a value without risking an error on bad input? A: Use a "safe" conversion function where available, such as SQL Server's TRYCAST() / TRYCONVERT(), which returns NULL instead of erroring.