SQL 53 🛢️ Data Type Conversion with CAST and CONVERT
A value has a type. The type determines what the value can do: a string can be concatenated, a number can be added, a date can be compared to another date. When a value needs to be used in a context where its type is not accepted, it must be converted. SQL provides two functions for this: CAST, which is the standard, and CONVERT, which is the SQL Server and MySQL variant. The conversion is not always lossless, and the way it fails depends on the type and the database.
This chapter covers type conversion in full. You will learn the CAST syntax, the CONVERT syntax, the implicit conversions that the database performs automatically, the explicit conversions that require the functions, the conversion failure modes, and the patterns that use conversion in real queries.
Key point: CAST(expression AS type) is the standard conversion function. CONVERT(type, expression) is the SQL Server form, and CONVERT(expression, type) is the MySQL form. Implicit conversion happens when the database automatically converts a value to the type the context requires. Explicit conversion is when the query uses CAST or CONVERT. The explicit form is preferred because it is predictable. A conversion that fails produces an error in most databases, or a NULL in some.
Why type conversion matters
The type mismatch problem. A column that stores a numeric ID as a string cannot be compared to an integer without a conversion. The comparison WHERE id = 42 fails or produces unexpected results if id is a VARCHAR. The CAST function makes the conversion explicit.
The display problem. A number that is displayed as 42 and a number that is displayed as 42.00 are the same value with different types. The CAST function converts a number to a string with a specified format, or a string to a number with a specified precision.
The portability problem. The CAST function is standard and supported by PostgreSQL, MySQL, SQL Server, and Oracle. The CONVERT function is not standard; its syntax differs between SQL Server and MySQL. Writing portable SQL means using CAST where possible.
The comparison problem. Comparing a date to a string produces different results depending on the conversion. WHERE date_col = '2026-01-08' works if the database converts the string to a date. WHERE date_col = 20260108 may or may not. The explicit conversion removes the ambiguity.
The failure problem. A conversion that cannot be performed produces an error. Converting the string 'abc' to an integer fails. Converting an integer to a string always succeeds. Converting a string to a date depends on the format. Knowing which conversions succeed and which fail is knowing when the query will work.
a. The CAST function
The CAST function converts an expression to a specified type.
SELECT CAST('42' AS integer); -- 42
SELECT CAST(42 AS text); -- '42'
SELECT CAST('2026-01-08' AS date); -- 2026-01-08
SELECT CAST(42.7 AS integer); -- 43 (rounds)
SELECT CAST(42.7 AS numeric(10,2)); -- 42.70The syntax is CAST(expression AS type). The type is the target type. The expression is the value to convert.
The common conversions:
| From | To | Result |
|---|---|---|
'42' | integer | 42 |
'42.7' | numeric | 42.7 |
42 | text | '42' |
42.7 | integer | 43 |
'2026-01-08' | date | 2026-01-08 |
'2026-01-08 10:30' | timestamp | 2026-01-08 10:30:00 |
'true' | boolean | true |
true | text | 'true' |
The conversion from a string to a number succeeds only if the string is a valid number. CAST('42' AS integer) succeeds. CAST('abc' AS integer) fails with an error.
SELECT CAST('abc' AS integer);
-- ERROR: invalid input syntax for type integer: "abc"The conversion from a number to a string always succeeds.
SELECT CAST(42 AS text); -- '42'
SELECT CAST(42.7 AS text); -- '42.7'
SELECT CAST(-42 AS text); -- '-42'The conversion from a number to an integer rounds. CAST(42.7 AS integer) is 43. The rounding is half away from zero for the numeric type.
SELECT CAST(42.4 AS integer); -- 42
SELECT CAST(42.5 AS integer); -- 43
SELECT CAST(42.6 AS integer); -- 43
SELECT CAST(-42.5 AS integer); -- -43The conversion from a string to a date depends on the format. The standard format is YYYY-MM-DD. A string in a different format may fail or be misinterpreted.
SELECT CAST('2026-01-08' AS date); -- 2026-01-08
SELECT CAST('01/08/2026' AS date); -- depends on localeThe CAST function is the standard. It is supported by PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.
The :: operator is the PostgreSQL shorthand for CAST.
SELECT '42'::integer; -- 42
SELECT 42::text; -- '42'
SELECT '2026-01-08'::date; -- 2026-01-08The :: is more concise but is not standard. It is PostgreSQL-specific.
b. The CONVERT function
The CONVERT function is not standard. It has two forms, one in SQL Server and one in MySQL.
In SQL Server, the syntax is CONVERT(type, expression, style). The style is an optional integer that specifies the format for date and time conversions.
SELECT CONVERT(integer, '42'); -- 42
SELECT CONVERT(varchar, 42); -- '42'
SELECT CONVERT(date, '2026-01-08'); -- 2026-01-08
SELECT CONVERT(varchar, GETDATE(), 23); -- '2026-01-08' (ISO 8601)
SELECT CONVERT(varchar, GETDATE(), 101); -- '01/08/2026' (US)The style codes are documented by Microsoft. The common ones:
| Style | Format |
|---|---|
| 1 | mm/dd/yy |
| 3 | dd/mm/yy |
| 23 | yyyy-mm-dd |
| 101 | mm/dd/yyyy |
| 103 | dd/mm/yyyy |
| 112 | yyyymmdd |
| 120 | yyyy-mm-dd hh:mi:ss |
| 126 | ISO 8601 |
| 127 | ISO 8601 with timezone |
In MySQL, the syntax is CONVERT(expression, type) or CONVERT(expression USING charset). The type form converts between types, and the charset form converts between character sets.
SELECT CONVERT('42', SIGNED); -- 42
SELECT CONVERT('42', UNSIGNED); -- 42
SELECT CONVERT(42, CHAR); -- '42'
SELECT CONVERT('2026-01-08', DATE); -- 2026-01-08
SELECT CONVERT('abc' USING utf8mb4); -- charset conversionThe MySQL types are SIGNED, UNSIGNED, CHAR, DATE, DATETIME, TIME, DECIMAL, BINARY, and others.
The CONVERT function in MySQL and SQL Server has different argument orders and different type names. The CAST function is the portable form for the type conversions that both support.
Oracle has no CONVERT function for types. It uses TO_NUMBER, TO_CHAR, and TO_DATE.
SELECT TO_NUMBER('42') FROM dual; -- 42
SELECT TO_CHAR(42) FROM dual; -- '42'
SELECT TO_DATE('2026-01-08', 'YYYY-MM-DD') FROM dual;PostgreSQL has no CONVERT function for types. It uses CAST and the :: operator.
c. Implicit conversion and conversion failures
The database performs implicit conversions automatically when the context requires a type.
SELECT 1 + '2'; -- 3 in MySQL, error in PostgreSQLIn MySQL, the string '2' is implicitly converted to the number 2, and the addition succeeds. In PostgreSQL, the addition of an integer and a string is an error; the conversion must be explicit.
-- PostgreSQL
SELECT 1 + '2';
-- ERROR: operator does not exist: integer + unknown
SELECT 1 + '2'::integer; -- 3The implicit conversion rules differ between databases. PostgreSQL is strict; MySQL is permissive. The CAST function is the reliable way to convert because it is explicit and the same in every database.
The implicit conversion in a WHERE clause can produce unexpected results.
-- MySQL: the string is converted to a number
SELECT * FROM users WHERE id = '42'; -- works
SELECT * FROM users WHERE id = '42abc'; -- matches 42, not an errorIn MySQL, '42abc' is converted to 42 because the conversion reads the leading digits and ignores the rest. This is surprising and can produce wrong results. The explicit CAST makes the behavior predictable.
The conversion of a NULL produces a NULL regardless of the target type.
SELECT CAST(NULL AS integer); -- NULL
SELECT CAST(NULL AS text); -- NULL
SELECT CAST(NULL AS date); -- NULLThe conversion of an empty string to a number is an error in PostgreSQL and a zero in MySQL.
-- PostgreSQL
SELECT CAST('' AS integer);
-- ERROR: invalid input syntax for type integer: ""
-- MySQL
SELECT CAST('' AS SIGNED); -- 0The conversion of a string to a boolean is database-specific. PostgreSQL accepts 'true', 'false', 't', 'f', '1', '0'. MySQL accepts '1' and '0' as booleans and treats other strings as 0.
-- PostgreSQL
SELECT CAST('true' AS boolean); -- true
SELECT CAST('1' AS boolean); -- true
-- MySQL
SELECT CAST('true' AS UNSIGNED); -- 0 (not a number)The conversion of a date to a string uses the default format of the database.
-- PostgreSQL
SELECT CAST(CURRENT_DATE AS text); -- '2026-01-08'
-- MySQL
SELECT CAST(CURRENT_DATE AS CHAR); -- '2026-01-08'
-- SQL Server
SELECT CONVERT(varchar, GETDATE(), 23); -- '2026-01-08'The conversion of a timestamp to a date truncates the time.
SELECT CAST(NOW() AS date); -- 2026-01-08The conversion of a date to a timestamp sets the time to midnight.
SELECT CAST(CURRENT_DATE AS timestamp); -- 2026-01-08 00:00:00The conversion between numeric types can lose precision.
SELECT CAST(42.7 AS integer); -- 43 (rounds)
SELECT CAST(42.7 AS numeric(3,1)); -- 42.7
SELECT CAST(42.7 AS numeric(3,0)); -- 43
SELECT CAST(1234.5 AS numeric(5,2)); -- 1234.50
SELECT CAST(1234.5 AS numeric(2,0)); -- error (overflow)The numeric(p, s) type has p total digits and s decimal digits. A value that does not fit produces an error.
Complete Example Session
-- ============================================
-- PART 1: CAST STRING TO NUMBER
-- ============================================
SELECT CAST('42' AS integer); -- 42
SELECT CAST('42.7' AS numeric); -- 42.7
SELECT CAST('42.7' AS numeric(10,2)); -- 42.70-- ============================================
-- PART 2: CAST NUMBER TO STRING
-- ============================================
SELECT CAST(42 AS text); -- '42'
SELECT CAST(42.7 AS text); -- '42.7'-- ============================================
-- PART 3: CAST TO INTEGER ROUNDS
-- ============================================
SELECT CAST(42.4 AS integer); -- 42
SELECT CAST(42.5 AS integer); -- 43
SELECT CAST(42.6 AS integer); -- 43-- ============================================
-- PART 4: CAST STRING TO DATE
-- ============================================
SELECT CAST('2026-01-08' AS date); -- 2026-01-08
SELECT CAST('2026-01-08 10:30' AS timestamp);-- ============================================
-- PART 5: POSTGRESQL :: OPERATOR
-- ============================================
SELECT '42'::integer; -- 42
SELECT 42::text; -- '42'
SELECT '2026-01-08'::date; -- 2026-01-08-- ============================================
-- PART 6: SQL SERVER CONVERT
-- ============================================
SELECT CONVERT(integer, '42');
SELECT CONVERT(varchar, 42);
SELECT CONVERT(varchar, GETDATE(), 23);-- ============================================
-- PART 7: MYSQL CONVERT
-- ============================================
SELECT CONVERT('42', SIGNED);
SELECT CONVERT(42, CHAR);
SELECT CONVERT('2026-01-08', DATE);-- ============================================
-- PART 8: IMPLICIT CONVERSION
-- ============================================
-- PostgreSQL: strict
SELECT 1 + '2'::integer; -- 3
-- MySQL: permissive
SELECT 1 + '2'; -- 3-- ============================================
-- PART 9: CONVERSION FAILURE
-- ============================================
-- PostgreSQL
SELECT CAST('abc' AS integer);
-- ERROR: invalid input syntax for type integer: "abc"-- ============================================
-- PART 10: SAFE CONVERSION WITH NULLIF
-- ============================================
SELECT CAST(NULLIF(value, '') AS integer) FROM data;
-- Empty string becomes NULL, then NULLThe ten parts covered casting a string to a number, a number to a string, the rounding behavior, casting to a date, the :: operator, CONVERT in SQL Server, CONVERT in MySQL, implicit conversion, conversion failure, and safe conversion.
Quick Reference
CAST Syntax
| Database | Syntax |
|---|---|
| Standard | CAST(expr AS type) |
| PostgreSQL | expr::type |
| MySQL | CAST(expr AS type) |
| SQL Server | CAST(expr AS type) |
| Oracle | CAST(expr AS type) |
CONVERT Syntax
| Database | Syntax |
|---|---|
| SQL Server | CONVERT(type, expr [, style]) |
| MySQL | CONVERT(expr, type) or CONVERT(expr USING charset) |
| Oracle | No CONVERT for types; use TO_* functions |
| PostgreSQL | No CONVERT for types; use CAST |
Common Types
| Type | Description |
|---|---|
integer / int | Whole number |
numeric(p,s) | Fixed precision |
decimal(p,s) | Same as numeric |
float / real | Floating point |
text / varchar | String |
char(n) | Fixed-length string |
date | Date |
timestamp | Date and time |
boolean | True/false |
uuid | UUID |
Conversion Behavior
| From | To | Result |
|---|---|---|
'42' | integer | 42 |
'42.7' | integer | Error (PostgreSQL) |
42.7 | integer | 43 (rounds) |
42 | text | '42' |
'abc' | integer | Error |
NULL | any | NULL |
'' | integer | Error (PostgreSQL), 0 (MySQL) |
'true' | boolean | true (PostgreSQL) |
SQL Server Date Styles
| Style | Format |
|---|---|
| 23 | yyyy-mm-dd |
| 101 | mm/dd/yyyy |
| 103 | dd/mm/yyyy |
| 112 | yyyymmdd |
| 120 | yyyy-mm-dd hh:mi:ss |
| 126 | ISO 8601 |
Best Practices
✅ Do This:
-- Use CAST for portability
SELECT CAST('42' AS integer); -- ✅
-- Use explicit conversion in comparisons
WHERE id = CAST('42' AS integer); -- ✅
-- Use NULLIF for safe conversion
SELECT CAST(NULLIF(value, '') AS integer) FROM data; -- ✅
-- Use numeric for exact decimal arithmetic
SELECT CAST(amount AS numeric(10,2)) FROM orders; -- ✅
-- Use CONVERT with a style for dates in SQL Server
SELECT CONVERT(varchar, GETDATE(), 23); -- ✅
-- Cast one operand to avoid integer division
SELECT CAST(total AS numeric) / count FROM stats; -- ✅❌ Don’t Do This:
-- Don't rely on implicit conversion
WHERE id = '42'; -- may work or fail -- ⚠️
-- Don't convert a string that may not be a number
SELECT CAST(user_input AS integer); -- error if not numeric -- ❌
-- Don't use CONVERT in PostgreSQL
SELECT CONVERT(integer, '42'); -- no such function -- ❌
-- Don't assume the date format
SELECT CAST('01/08/2026' AS date); -- locale-dependent -- ⚠️
-- Don't cast to a type that cannot hold the value
SELECT CAST(12345 AS numeric(2,0)); -- overflow -- ❌
-- Don't compare a number to a string without conversion
WHERE num_col = '42'; -- may not use the index -- ⚠️
-- Don't forget that CAST rounds when converting to integer
SELECT CAST(42.5 AS integer); -- 43, not 42 -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Conversion error | String is not a number | Validate or use NULLIF |
| Wrong date | Format mismatch | Use ISO format |
| Rounded integer | CAST rounds | Use TRUNC or FLOOR |
| Overflow | Value too large for type | Use a larger type |
| Implicit conversion surprise | Database rules differ | Use explicit CAST |
CONVERT not found | Database does not have it | Use CAST |
| Index not used | Type mismatch in WHERE | Cast the literal, not the column |
Real-World Examples
1. String to Number
SELECT CAST(quantity AS integer) FROM order_items;2. Number to String
SELECT 'Order #' || CAST(order_id AS text) FROM orders;3. Date to String
SELECT CAST(order_date AS text) FROM orders;4. String to Date
SELECT CAST('2026-01-08' AS date);5. Timestamp to Date
SELECT CAST(created_at AS date) FROM users;6. Percentage
SELECT CAST(100.0 * successful / NULLIF(total, 0) AS numeric(5,2))
FROM stats;7. Safe Conversion
SELECT CAST(NULLIF(value, '') AS integer) FROM data;8. SQL Server Date Format
SELECT CONVERT(varchar, GETDATE(), 23);9. Boolean to String
SELECT CAST(is_active AS text) FROM users;10. Numeric Precision
SELECT CAST(price AS numeric(10,2)) FROM products;Visual
CAST Function
┌─────────────────────────────────────────────────────────────┐
│ CAST(expression AS type) │
│ │
│ CAST('42' AS integer) → 42 │
│ CAST(42 AS text) → '42' │
│ CAST('2026-01-08' AS date) → 2026-01-08 │
│ CAST(42.7 AS integer) → 43 (rounds) │
│ CAST(42.7 AS numeric(10,2)) → 42.70 │
│ │
│ PostgreSQL shorthand: │
│ '42'::integer → 42 │
│ 42::text → '42' │
│ │
└─────────────────────────────────────────────────────────────┘CAST vs CONVERT
┌─────────────────────────────────────────────────────────────┐
│ CAST (standard) │
│ CAST(expr AS type) │
│ Supported by: PostgreSQL, MySQL, SQL Server, Oracle, SQLite│
│ │
│ CONVERT (SQL Server) │
│ CONVERT(type, expr, style) │
│ The style argument formats dates │
│ │
│ CONVERT (MySQL) │
│ CONVERT(expr, type) │
│ The argument order is reversed │
│ │
│ Use CAST for portability. │
│ │
└─────────────────────────────────────────────────────────────┘Conversion Failure
┌─────────────────────────────────────────────────────────────┐
│ CAST('42' AS integer) → 42 │
│ CAST('abc' AS integer) → ERROR │
│ │
│ The error aborts the query. │
│ │
│ Safe conversion: │
│ CAST(NULLIF(value, '') AS integer) │
│ → converts empty string to NULL, then NULL │
│ │
│ Or use a CASE: │
│ CASE WHEN value ~ '^[0-9]+$' │
│ THEN CAST(value AS integer) │
│ ELSE NULL END │
│ │
└─────────────────────────────────────────────────────────────┘Numeric Precision
┌─────────────────────────────────────────────────────────────┐
│ numeric(p, s) │
│ p = total digits │
│ s = decimal digits │
│ │
│ numeric(5,2) → 123.45 │
│ numeric(5,2) → 1234.5 (error: 5 digits, 1 decimal) │
│ numeric(10,2) → 12345678.90 │
│ │
│ CAST(1234.5 AS numeric(5,2)) → 1234.50 │
│ CAST(1234.5 AS numeric(3,0)) → error (overflow) │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Standard | CAST(expr AS type) |
| PostgreSQL shorthand | expr::type |
| SQL Server | CONVERT(type, expr, style) |
| MySQL | CONVERT(expr, type) |
| Oracle | TO_NUMBER, TO_CHAR, TO_DATE |
| String to number | CAST('42' AS integer) |
| Number to string | CAST(42 AS text) |
| String to date | CAST('2026-01-08' AS date) |
| Rounding | CAST(42.7 AS integer) → 43 |
NULL conversion | CAST(NULL AS type) → NULL |
| Failure | Error on invalid input |
| Safe conversion | CAST(NULLIF(value, '') AS integer) |
Key takeaways:
CASTis the standard conversion function. It is supported by every major database. The syntax isCAST(expression AS type). The PostgreSQL::operator is a shorthand forCAST.CONVERTis not standard. SQL Server usesCONVERT(type, expr, style), and MySQL usesCONVERT(expr, type). The argument order and the type names differ. UseCASTfor portability.- Implicit conversion is database-specific. PostgreSQL is strict; MySQL is permissive. The
CASTfunction is explicit and predictable. Use it when the conversion matters. - Conversion to integer rounds.
CAST(42.7 AS integer)is43. The rounding is half away from zero for the numeric type. UseTRUNCorFLOORif the fractional part should be discarded. - Conversion from a string can fail. A string that is not a valid number produces an error. The
NULLIFpattern converts an empty string toNULLbefore the cast, which prevents the error. - The
numeric(p, s)type has a fixed precision. A value that does not fit produces an error. Choosepandslarge enough for the values. - The date format depends on the database. Use the ISO format
YYYY-MM-DDfor the most reliable conversion. SQL Server’sCONVERTwith style23produces the ISO format.
Remember: Type conversion is how SQL handles values that do not have the type the context requires. The CAST function is the standard, and the explicit form is the predictable form. The implicit conversions differ between databases; the explicit conversions are the same. Use CAST for portability. Use CONVERT with a style for date formatting in SQL Server. Use NULLIF for safe conversion. And remember that a conversion can fail—the failure is the database telling you that the value is not what the type expects.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!