SQL CAST and CONVERT for Type Conversion

Data does not always arrive in the type you need. A price stored as text cannot participate in arithmetic. A date buried inside a string cannot be compared to CURRENT_DATE. An integer divided by another integer silently drops the decimal portion. A numeric ID needs to be concatenated into a message string. These situations happen constantly in real-world databases.

SQL CAST is the standard tool for converting a value from one data type to another. It lets you explicitly tell the database "treat this text as a number" or "turn this timestamp into a date." Understanding type conversion is essential because many subtle bugs, from incorrect calculations to failed queries, trace back to values being in the wrong type at the wrong time.

This guide covers explicit conversion with CAST and CONVERT, explains how implicit conversion works behind the scenes, walks through the most common conversion scenarios with examples, and highlights the pitfalls that catch developers off guard.

The Sample Data

All examples use these tables:

CREATE TABLE raw_imports (
    record_id     INT PRIMARY KEY,
    product_name  VARCHAR(100),
    price_text    VARCHAR(20),
    quantity_text VARCHAR(10),
    order_date_text VARCHAR(20),
    is_active_text  VARCHAR(5),
    rating_text   VARCHAR(10)
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    TIMESTAMP,
    total_amount  DECIMAL(10,2),
    item_count    INT
);

INSERT INTO raw_imports VALUES
(1, 'Wireless Mouse',     '25.99',  '150', '2024-06-15', 'true',  '4.5'),
(2, 'USB-C Cable',        '9.99',   '300', '2024-06-18', 'true',  '3.8'),
(3, 'Notebook A5',        '4.50',   '500', '06/20/2024', 'false', '4.2'),
(4, 'Mechanical Keyboard', '89.99',  '45',  '2024-06-22', 'true',  '4.9'),
(5, 'Desk Lamp',          'N/A',    '0',   '2024-06-25', 'false', '3.0'),
(6, 'Standing Desk',      '299.99', '12',  '2024-06-28', 'true',  NULL);

INSERT INTO orders VALUES
(1001, 1, '2024-01-15 09:30:00', 155.97, 3),
(1002, 2, '2024-03-22 14:15:00', 299.99, 1),
(1003, 1, '2024-06-05 11:00:00', 45.48,  2),
(1004, 3, '2024-06-10 16:45:00', 89.99,  1),
(1005, 2, '2024-06-14 08:20:00', 9.99,   4);

note

Notice that raw_imports stores everything as VARCHAR, simulating data imported from CSV files, spreadsheets, or external APIs where all values arrive as text.

CAST: The SQL Standard

CAST is the SQL standard syntax for explicit type conversion. It works in every major database: PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.

Syntax

CAST(expression AS target_type)

The expression can be a column, a literal value, or any valid SQL expression. The target type is the data type you want the result to be.

String to Number

The most common conversion: turning text that contains a number into an actual numeric type.

SELECT
    product_name,
    price_text,
    CAST(price_text AS DECIMAL(10,2)) AS price_number,
    CAST(quantity_text AS INT) AS quantity
FROM raw_imports
WHERE price_text != 'N/A';

product_name	price_text	price_number	quantity
Wireless Mouse	25.99	25.99	150
USB-C Cable	9.99	9.99	300
Notebook A5	4.50	4.50	500
Mechanical Keyboard	89.99	89.99	45
Standing Desk	299.99	299.99	12

Once converted, you can perform arithmetic:

SELECT
    product_name,
    CAST(price_text AS DECIMAL(10,2)) * CAST(quantity_text AS INT) AS inventory_value
FROM raw_imports
WHERE price_text != 'N/A';

product_name	inventory_value
Wireless Mouse	3898.50
USB-C Cable	2997.00
Notebook A5	2250.00
Mechanical Keyboard	4049.55
Standing Desk	3599.88

Number to String

Converting numbers to text for concatenation or formatted output:

SELECT
    'Order #' || CAST(order_id AS VARCHAR(10)) || ' totals $' || CAST(total_amount AS VARCHAR(20)) AS summary
FROM orders;

summary
Order #1001 totals $155.97
Order #1002 totals $299.99
Order #1003 totals $45.48
Order #1004 totals $89.99
Order #1005 totals $9.99

info

In many databases, CONCAT() performs implicit conversion, so CONCAT('Order #', order_id) works without explicit CAST. However, the || operator in PostgreSQL does not auto-convert numbers to strings, so CAST is required.

-- PostgreSQL: fails without CAST
SELECT 'Total: ' || 42;
-- ERROR: operator does not exist: text || integer

-- PostgreSQL: works with CAST
SELECT 'Total: ' || CAST(42 AS VARCHAR);
-- Result: 'Total: 42'

-- PostgreSQL: alternative shorthand
SELECT 'Total: ' || 42::TEXT;
-- Result: 'Total: 42'

String to Date

Converting text dates into proper date types:

SELECT
    product_name,
    order_date_text,
    CAST(order_date_text AS DATE) AS order_date
FROM raw_imports
WHERE order_date_text LIKE '____-__-__';

product_name	order_date_text	order_date
Wireless Mouse	2024-06-15	2024-06-15
USB-C Cable	2024-06-18	2024-06-18
Mechanical Keyboard	2024-06-22	2024-06-22
Desk Lamp	2024-06-25	2024-06-25
Standing Desk	2024-06-28	2024-06-28

Notice we filtered out record 3 (Notebook A5) because its date format 06/20/2024 is MM/DD/YYYY, which CAST may not recognize depending on the database's locale settings.

Timestamp to Date (Stripping Time)

SELECT
    order_id,
    order_date,
    CAST(order_date AS DATE) AS date_only
FROM orders;

order_id	order_date	date_only
1001	2024-01-15 09:30:00	2024-01-15
1002	2024-03-22 14:15:00	2024-03-22
1003	2024-06-05 11:00:00	2024-06-05
1004	2024-06-10 16:45:00	2024-06-10
1005	2024-06-14 08:20:00	2024-06-14

Integer to Decimal (Fixing Integer Division)

One of the most practical uses of CAST: preventing integer division truncation.

-- Without CAST: integer division truncates
SELECT 7 / 2 AS int_result;
-- PostgreSQL/SQL Server/SQLite: 3 (not 3.5!)

-- With CAST: decimal division preserves precision
SELECT CAST(7 AS DECIMAL(10,2)) / 2 AS decimal_result;
-- Result: 3.50

Practical example with real columns:

SELECT
    order_id,
    total_amount,
    item_count,
    total_amount / item_count AS without_cast,
    total_amount / CAST(item_count AS DECIMAL(10,2)) AS with_cast
FROM orders;

order_id	total_amount	item_count	without_cast	with_cast
1001	155.97	3	51.9900000000000000	51.9900000000000000
1002	299.99	1	299.9900000000000000	299.9900000000000000
1003	45.48	2	22.7400000000000000	22.7400000000000000
1004	89.99	1	89.9900000000000000	89.9900000000000000
1005	9.99	4	2.4975000000000000	2.4975000000000000

In this case total_amount is already DECIMAL, so dividing by an INT produces a decimal result in most databases. But when both columns are integers, the cast becomes critical.

tip

The easiest way to force decimal division without CAST is to multiply by 1.0:

SELECT 7 * 1.0 / 2 AS result;
-- Result: 3.5

This works because multiplying an integer by 1.0 promotes it to a decimal type.

PostgreSQL Shorthand: :: Operator

PostgreSQL offers a concise casting syntax using the :: operator:

-- These are equivalent in PostgreSQL:
SELECT CAST('42' AS INTEGER);
SELECT '42'::INTEGER;

SELECT CAST(order_date AS DATE);
SELECT order_date::DATE;

SELECT CAST(price AS VARCHAR);
SELECT price::VARCHAR;
SELECT price::TEXT;

The :: operator is PostgreSQL-specific and not portable to other databases, but it is so concise that it is used almost universally in PostgreSQL code:

SELECT
    product_name,
    price_text::DECIMAL(10,2) AS price,
    quantity_text::INT AS quantity,
    order_date_text::DATE AS order_date
FROM raw_imports
WHERE price_text != 'N/A'
  AND order_date_text LIKE '____-__-__';

product_name	price	quantity	order_date
Wireless Mouse	25.99	150	2024-06-15
USB-C Cable	9.99	300	2024-06-18
Mechanical Keyboard	89.99	45	2024-06-22
Standing Desk	299.99	12	2024-06-28

info

The :: syntax supports all the same conversions as CAST. It is purely a syntactic shorthand. When writing cross-database SQL, use CAST(). When writing PostgreSQL-only code, :: is idiomatic and preferred.

CONVERT: SQL Server's Extended Conversion

SQL Server supports both CAST (standard) and its own CONVERT function, which adds a style parameter for controlling date and number formatting during conversion.

Syntax

CONVERT(target_type, expression, style)

The third argument (style) is optional and is primarily used with date/time conversions.

Date Formatting with CONVERT Styles

SELECT
    order_date,
    CONVERT(VARCHAR, order_date, 23)  AS iso_format,      -- yyyy-mm-dd
    CONVERT(VARCHAR, order_date, 101) AS us_format,        -- mm/dd/yyyy
    CONVERT(VARCHAR, order_date, 103) AS uk_format,        -- dd/mm/yyyy
    CONVERT(VARCHAR, order_date, 104) AS german_format,    -- dd.mm.yyyy
    CONVERT(VARCHAR, order_date, 108) AS time_only,        -- hh:mm:ss
    CONVERT(VARCHAR, order_date, 120) AS odbc_format       -- yyyy-mm-dd hh:mm:ss
FROM orders
WHERE order_id = 1001;

order_date	iso_format	us_format	uk_format	german_format	time_only	odbc_format
2024-01-15 09:30:00.0000000	2024-01-15	01/15/2024	15/01/2024	15.01.2024	09:30:00	2024-01-15 09:30:00

Common SQL Server CONVERT style codes:

Style	Format	Example
23	`yyyy-mm-dd`	2024-06-15
101	`mm/dd/yyyy`	06/15/2024
103	`dd/mm/yyyy`	15/06/2024
104	`dd.mm.yyyy`	15.06.2024
108	`hh:mm:ss`	14:30:00
112	`yyyymmdd`	20240615
120	`yyyy-mm-dd hh:mm:ss`	2024-06-15 14:30:00
126	ISO 8601	2024-06-15T14:30:00

Parsing Date Strings with CONVERT

-- SQL Server: parse a date string in a specific format
SELECT CONVERT(DATE, '15/06/2024', 103) AS parsed_date;
-- Result: 2024-06-15

SELECT CONVERT(DATE, '06/15/2024', 101) AS parsed_date;
-- Result: 2024-06-15

The style code tells SQL Server which format the input string uses, preventing ambiguity.

CAST vs CONVERT in SQL Server

Feature	`CAST`	`CONVERT`
SQL Standard	Yes	No (SQL Server only)
Date formatting styles	No	Yes
Readability	Slightly clearer	Slightly more compact
Performance	Same	Same
Portability	All databases	SQL Server only

tip

Use CAST for basic type conversion and CONVERT only when you need SQL Server's date/number formatting styles. If your code might run on other databases, stick with CAST.

MySQL-Specific Conversions

MySQL supports CAST with a slightly different set of target types:

-- MySQL target types in CAST
SELECT
    CAST('42' AS SIGNED)         AS int_val,      -- integer
    CAST('42' AS UNSIGNED)       AS uint_val,     -- unsigned integer
    CAST('3.14' AS DECIMAL(5,2)) AS dec_val,      -- decimal
    CAST('2024-06-15' AS DATE)   AS date_val,     -- date
    CAST('14:30:00' AS TIME)     AS time_val,     -- time
    CAST(42 AS CHAR)             AS str_val;      -- string

int_val	uint_val	dec_val	date_val	time_val	str_val
42	42	3.14	2024-06-15	14:30:00	42

warning

MySQL uses SIGNED and UNSIGNED instead of INT or INTEGER in CAST:

-- MySQL
SELECT CAST('42' AS SIGNED);         -- Works
SELECT CAST('42' AS INT);            -- ERROR in older MySQL versions
SELECT CAST('42' AS SIGNED INTEGER); -- Also works

MySQL 8.0+ added support for CAST(... AS FLOAT) and CAST(... AS DOUBLE).

MySQL CONVERT Function

MySQL's CONVERT is different from SQL Server's. It is used for character set conversion, not date formatting:

-- MySQL: CONVERT for character set conversion
SELECT CONVERT('hello' USING utf8mb4);
SELECT CONVERT(column_name, CHAR(50));  -- type conversion (alternative CAST syntax)

-- MySQL: date formatting uses DATE_FORMAT, not CONVERT
SELECT DATE_FORMAT(order_date, '%d/%m/%Y') FROM orders;

Implicit vs Explicit Conversion

Not all type conversions require CAST. Databases automatically convert between compatible types in many situations. This is called implicit conversion (or type coercion).

Where Implicit Conversion Happens

Arithmetic operations:

-- INT + DECIMAL: INT is implicitly promoted to DECIMAL
SELECT 10 + 3.5;
-- Result: 13.5 (no CAST needed)

-- INT * FLOAT: INT promoted to FLOAT
SELECT 7 * 2.5;
-- Result: 17.5

Comparison with literals:

-- Comparing a DATE column to a string literal
SELECT * FROM orders WHERE order_date > '2024-06-01';
-- The string '2024-06-01' is implicitly converted to a timestamp

INSERT with compatible types:

-- Inserting a string into a DECIMAL column
INSERT INTO orders (order_id, total_amount) VALUES (1006, '199.99');
-- '199.99' is implicitly converted to DECIMAL(10,2)

CONCAT in MySQL:

-- MySQL: CONCAT implicitly converts numbers to strings
SELECT CONCAT('Order #', 1001, ' total: $', 155.97);
-- Result: 'Order #1001 total: $155.97'

When Implicit Conversion Goes Wrong

Implicit conversion is convenient but can cause problems:

Silent data loss:

-- Inserting a decimal into an INT column
INSERT INTO orders (order_id, item_count) VALUES (1006, 3.7);
-- item_count becomes 3 (or 4, depending on database)
-- No error, no warning. The .7 is silently lost.

Unexpected comparison behavior:

-- MySQL: comparing a VARCHAR column to a number
SELECT * FROM raw_imports WHERE price_text = 0;

In MySQL, this compares every row because MySQL converts price_text to a number. Any non-numeric string (like 'N/A') converts to 0, matching the condition. This returns the Desk Lamp row unexpectedly.

-- MySQL's implicit conversion produces surprising results
SELECT 'N/A' = 0;        -- Result: 1 (TRUE!)
SELECT 'hello' = 0;      -- Result: 1 (TRUE!)
SELECT '5abc' + 0;       -- Result: 5
SELECT 'abc5' + 0;       -- Result: 0

danger

MySQL's implicit string-to-number conversion is one of the most dangerous behaviors in SQL. Any string that does not start with a digit converts to 0. This means WHERE varchar_column = 0 matches every non-numeric string in the table, potentially returning thousands of incorrect results.

Always use explicit CAST when comparing across types, or compare strings to strings:

-- Safe: compare as strings
WHERE price_text = '0'

-- Safe: explicit conversion with error handling
WHERE CAST(price_text AS DECIMAL(10,2)) = 0

Performance impact of implicit conversion:

-- If customer_id is INT but you compare to a string:
SELECT * FROM orders WHERE customer_id = '2';
-- The database may convert EVERY customer_id to VARCHAR for comparison
-- This prevents index usage and causes a full table scan

Explicit Is Always Better

-- Implicit: works but unclear intent, risky
SELECT * FROM orders WHERE order_date > '2024-06-01';

-- Explicit: clear intent, no ambiguity
SELECT * FROM orders WHERE order_date > CAST('2024-06-01' AS TIMESTAMP);

-- Explicit: guaranteed correct division
SELECT CAST(total_amount AS DECIMAL(12,4)) / CAST(item_count AS DECIMAL(12,4))
FROM orders;

tip

Rule of thumb: If you are mixing types in a comparison, calculation, or concatenation, use explicit CAST. It documents your intent, prevents subtle bugs, and makes the query's behavior predictable across different databases and data values.

Common Conversion Scenarios

Scenario 1: Cleaning Imported CSV Data

Raw imports often arrive as all-text. Convert to proper types for analysis:

SELECT
    record_id,
    product_name,
    CASE
        WHEN price_text ~ '^\d+\.?\d*$' THEN CAST(price_text AS DECIMAL(10,2))
        ELSE NULL
    END AS price,
    CAST(quantity_text AS INT) AS quantity,
    CASE
        WHEN order_date_text ~ '^\d{4}-\d{2}-\d{2}$' THEN CAST(order_date_text AS DATE)
        ELSE NULL
    END AS order_date,
    CAST(is_active_text AS BOOLEAN) AS is_active
FROM raw_imports;

record_id	product_name	price	quantity	order_date	is_active
1	Wireless Mouse	25.99	150	2024-06-15	true
2	USB-C Cable	9.99	300	2024-06-18	true
3	Notebook A5	4.50	500	NULL	false
4	Mechanical Keyboard	89.99	45	2024-06-22	true
5	Desk Lamp	NULL	0	2024-06-25	false
6	Standing Desk	299.99	12	2024-06-28	true

The CASE expressions handle invalid values ('N/A' for price, wrong date format) by returning NULL instead of crashing.

Scenario 2: Building Display Strings

SELECT
    order_id,
    'Ordered on ' ||
        TO_CHAR(order_date, 'Mon DD, YYYY') ||
        ' - ' ||
        CAST(item_count AS VARCHAR) ||
        ' item(s) totaling $' ||
        CAST(total_amount AS VARCHAR)
    AS order_summary
FROM orders;

order_summary
Ordered on Jan 15, 2024 - 3 item(s) totaling $155.97
Ordered on Mar 22, 2024 - 1 item(s) totaling $299.99
Ordered on Jun 05, 2024 - 2 item(s) totaling $45.48
Ordered on Jun 10, 2024 - 1 item(s) totaling $89.99
Ordered on Jun 14, 2024 - 4 item(s) totaling $9.99

Scenario 3: Grouping by Date Components

-- Group timestamps by date (strip time component)
SELECT
    CAST(order_date AS DATE) AS order_day,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
ORDER BY order_day;

order_day	order_count	daily_total
2024-01-15	1	155.97
2024-03-22	1	299.99
2024-06-05	1	45.48
2024-06-10	1	89.99
2024-06-14	1	9.99

Scenario 4: Conditional Type Conversion

Handle mixed data with CASE and CAST:

SELECT
    product_name,
    price_text,
    CASE
        WHEN price_text = 'N/A' THEN 0.00
        WHEN price_text IS NULL THEN 0.00
        ELSE CAST(price_text AS DECIMAL(10,2))
    END AS price
FROM raw_imports;

product_name	price_text	price
Wireless Mouse	25.99	25.99
USB-C Cable	9.99	9.99
Notebook A5	4.50	4.50
Mechanical Keyboard	89.99	89.99
Desk Lamp	N/A	0.00
Standing Desk	299.99	299.99

Scenario 5: Safe Conversion Functions

PostgreSQL and SQL Server offer "try" variants that return NULL instead of errors for invalid conversions:

PostgreSQL (using exception handling in a function or using regex):

-- PostgreSQL: validate before casting
SELECT
    price_text,
    CASE
        WHEN price_text ~ '^\d+\.?\d*$'
        THEN CAST(price_text AS DECIMAL(10,2))
        ELSE NULL
    END AS safe_price
FROM raw_imports;

SQL Server: TRY_CAST and TRY_CONVERT:

-- SQL Server: TRY_CAST returns NULL instead of error
SELECT
    price_text,
    TRY_CAST(price_text AS DECIMAL(10,2)) AS safe_price,
    TRY_CONVERT(DATE, order_date_text, 23) AS safe_date
FROM raw_imports;

tip

TRY_CAST (SQL Server) is invaluable for data cleaning. It never throws an error. If the conversion fails, it silently returns NULL. Use it whenever you are working with external data that may contain invalid values.

-- Find rows with invalid data
SELECT *
FROM raw_imports
WHERE TRY_CAST(price_text AS DECIMAL(10,2)) IS NULL
  AND price_text IS NOT NULL;
-- Returns only the 'N/A' row

MySQL does not have TRY_CAST. Use CASE with validation or +0 for numeric testing:

-- MySQL: test if a string is numeric
SELECT price_text, price_text + 0 AS numeric_test FROM raw_imports;
-- Non-numeric strings convert to 0 with a warning

Common Conversion Pitfalls

Pitfall 1: CAST Failure Crashes the Entire Query

A single invalid value in any row causes CAST to fail for the whole query:

Crashes:

SELECT CAST(price_text AS DECIMAL(10,2)) FROM raw_imports;
-- ERROR: invalid input syntax for type numeric: "N/A"

The 'N/A' in record 5 cannot be converted to DECIMAL, and the entire query fails.

Fix: Filter or handle invalid values before casting:

-- Option A: Filter out bad rows
SELECT CAST(price_text AS DECIMAL(10,2))
FROM raw_imports
WHERE price_text NOT IN ('N/A', '', 'null');

-- Option B: CASE with validation
SELECT
    CASE
        WHEN price_text ~ '^\d+\.?\d*$' THEN CAST(price_text AS DECIMAL(10,2))
        ELSE NULL
    END AS safe_price
FROM raw_imports;

-- Option C: TRY_CAST (SQL Server only)
SELECT TRY_CAST(price_text AS DECIMAL(10,2)) FROM raw_imports;

Pitfall 2: Precision Loss When Casting Decimals

-- Casting a precise number to fewer decimal places
SELECT CAST(3.14159 AS DECIMAL(5,2)) AS truncated;
-- Result: 3.14 (digits after the 2nd decimal place are lost)

-- Casting a large number to a type that's too small
SELECT CAST(123456.789 AS DECIMAL(5,2));
-- ERROR: numeric field overflow (5 total digits can't hold 123456)

Always ensure the target type's precision and scale can accommodate your data.

Pitfall 3: Date Format Ambiguity

-- Is '01/02/2024' January 2nd or February 1st?
SELECT CAST('01/02/2024' AS DATE);

The answer depends on the database's locale settings:

US format (MM/DD/YYYY): January 2nd
European format (DD/MM/YYYY): February 1st

Fix: Use unambiguous ISO format (YYYY-MM-DD) or specify the format explicitly:

-- ISO format: always unambiguous
SELECT CAST('2024-01-02' AS DATE);
-- Always January 2nd, regardless of locale

-- PostgreSQL: TO_DATE with explicit format
SELECT TO_DATE('01/02/2024', 'MM/DD/YYYY');

-- MySQL: STR_TO_DATE with explicit format
SELECT STR_TO_DATE('01/02/2024', '%m/%d/%Y');

-- SQL Server: CONVERT with style code
SELECT CONVERT(DATE, '01/02/2024', 101);  -- US format
SELECT CONVERT(DATE, '01/02/2024', 103);  -- European format

warning

Always use ISO 8601 format (YYYY-MM-DD) for date strings in SQL. It is unambiguous, universally recognized by every database, and eliminates locale-dependent parsing errors.

-- Bad: ambiguous
WHERE order_date > '06/01/2024'

-- Good: unambiguous
WHERE order_date > '2024-06-01'

Pitfall 4: Boolean Conversion Inconsistency

Databases handle booleans differently:

-- PostgreSQL: native boolean type
SELECT CAST('true' AS BOOLEAN);    -- true
SELECT CAST('yes' AS BOOLEAN);     -- ERROR! (only 'true'/'false', 't'/'f', '1'/'0')
SELECT CAST(1 AS BOOLEAN);         -- true
SELECT CAST(0 AS BOOLEAN);         -- false

-- MySQL: no native BOOLEAN in CAST
SELECT CAST('true' AS UNSIGNED);   -- 0 (string converted to number)
SELECT 'true' = TRUE;              -- 0 (FALSE, because TRUE = 1)

-- SQL Server: no BOOLEAN type at all
SELECT CAST(1 AS BIT);             -- 1
SELECT CAST(0 AS BIT);             -- 0
SELECT CAST('true' AS BIT);        -- ERROR!

info

There is no portable way to cast strings like 'true'/'false' to booleans across all databases. Use CASE for cross-database compatibility:

SELECT
    CASE LOWER(is_active_text)
        WHEN 'true'  THEN TRUE
        WHEN 'yes'   THEN TRUE
        WHEN '1'     THEN TRUE
        WHEN 'false' THEN FALSE
        WHEN 'no'    THEN FALSE
        WHEN '0'     THEN FALSE
        ELSE NULL
    END AS is_active
FROM raw_imports;

Pitfall 5: NULL Handling in CAST

CAST(NULL AS any_type) always returns NULL. This is correct behavior, but it can surprise you when combined with other operations:

SELECT CAST(NULL AS INT);           -- NULL
SELECT CAST(NULL AS VARCHAR);       -- NULL
SELECT CAST(NULL AS INT) + 10;      -- NULL (not 10)
SELECT CAST(NULL AS VARCHAR) || 'hello';  -- NULL in PostgreSQL

Use COALESCE to provide fallback values:

SELECT
    product_name,
    COALESCE(CAST(rating_text AS DECIMAL(3,1)), 0.0) AS rating
FROM raw_imports;

product_name	rating
Wireless Mouse	4.5
USB-C Cable	3.8
Notebook A5	4.2
Mechanical Keyboard	4.9
Desk Lamp	3.0
Standing Desk	0.0

Pitfall 6: Casting in WHERE Prevents Index Usage

-- Slow: CAST on the column prevents index use
SELECT * FROM orders
WHERE CAST(order_date AS DATE) = '2024-06-10';

-- Fast: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2024-06-10'
  AND order_date < '2024-06-11';

-- Also fast: CAST on the literal, not the column
SELECT * FROM orders
WHERE order_date >= CAST('2024-06-10' AS TIMESTAMP)
  AND order_date < CAST('2024-06-11' AS TIMESTAMP);

tip

Never CAST the column side of a comparison when an index exists on that column. Cast the literal/parameter side instead. Applying a function or cast to a column forces the database to evaluate every row, bypassing any index.

Type Conversion Compatibility Reference

From → To	String to Number	Number to String	String to Date	Date to String	Integer to Decimal	Timestamp to Date
PostgreSQL	`CAST(x AS INT/DECIMAL)` or `x::INT`	`CAST(x AS VARCHAR)` or `x::TEXT`	`CAST(x AS DATE)` or `TO_DATE()`	`TO_CHAR()`	`CAST(x AS DECIMAL)`	`CAST(x AS DATE)` or `x::DATE`
MySQL	`CAST(x AS SIGNED/DECIMAL)`	`CAST(x AS CHAR)`	`CAST(x AS DATE)` or `STR_TO_DATE()`	`DATE_FORMAT()`	`CAST(x AS DECIMAL)`	`CAST(x AS DATE)` or `DATE(x)`
SQL Server	`CAST(x AS INT/DECIMAL)` or `TRY_CAST()`	`CAST(x AS VARCHAR)` or `CONVERT()`	`CAST(x AS DATE)` or `CONVERT(DATE, x, style)`	`CONVERT(VARCHAR, x, style)` or `FORMAT()`	`CAST(x AS DECIMAL)`	`CAST(x AS DATE)`
Oracle	`TO_NUMBER(x)`	`TO_CHAR(x)`	`TO_DATE(x, format)`	`TO_CHAR(x, format)`	`CAST(x AS NUMBER)`	`CAST(x AS DATE)` or `TRUNC(x)`

Quick Reference

Task	Syntax	Example
String to integer	`CAST(x AS INT)`	`CAST('42' AS INT)` → `42`
String to decimal	`CAST(x AS DECIMAL(p,s))`	`CAST('3.14' AS DECIMAL(5,2))` → `3.14`
Number to string	`CAST(x AS VARCHAR(n))`	`CAST(42 AS VARCHAR(10))` → `'42'`
String to date	`CAST(x AS DATE)`	`CAST('2024-06-15' AS DATE)`
Timestamp to date	`CAST(x AS DATE)`	`CAST(NOW() AS DATE)`
Fix integer division	`CAST(x AS DECIMAL)`	`CAST(7 AS DECIMAL) / 2` → `3.5`
PostgreSQL shorthand	`x::type`	`'42'::INT`, `NOW()::DATE`
SQL Server safe cast	`TRY_CAST(x AS type)`	`TRY_CAST('N/A' AS INT)` → `NULL`
SQL Server date format	`CONVERT(type, x, style)`	`CONVERT(VARCHAR, date, 23)`

Summary

SQL CAST is the standard mechanism for converting values between data types. It is fundamental to working with real-world data where types do not always match what your queries need.

Key takeaways:

CAST(expression AS type) is the SQL standard syntax supported by every major database. Use it as your default conversion tool.
CONVERT in SQL Server adds date/time formatting style codes that CAST cannot express. MySQL's CONVERT is different and handles character set conversion.
PostgreSQL's :: operator is a concise shorthand for CAST that is idiomatic in PostgreSQL code but not portable.
Implicit conversion happens automatically when the database can safely promote types (like INT to DECIMAL in arithmetic). But it can also cause silent bugs, especially in MySQL where string-to-number conversion treats non-numeric strings as 0.
Always prefer explicit conversion over implicit. It documents your intent, prevents subtle bugs, and makes queries portable.
Handle invalid data before casting. Use CASE with validation, TRY_CAST (SQL Server), or regex checks (PostgreSQL) to avoid query-crashing conversion errors.
Use ISO 8601 format (YYYY-MM-DD) for date strings to eliminate locale ambiguity.
Never cast the column side of a WHERE comparison when an index exists. Cast the literal side instead to preserve index usage.
Integer division silently truncates decimals in PostgreSQL, SQL Server, and SQLite. Cast at least one operand to DECIMAL or multiply by 1.0 to force decimal division.

Type conversion is one of those skills that seems simple until it bites you. Master CAST, understand when implicit conversion helps versus hurts, and always validate data before converting, and you will avoid an entire category of production bugs.

The Sample Data​

CAST: The SQL Standard​

Syntax​

String to Number​

Number to String​

String to Date​

Timestamp to Date (Stripping Time)​

Integer to Decimal (Fixing Integer Division)​

PostgreSQL Shorthand: :: Operator​

CONVERT: SQL Server's Extended Conversion​

Syntax​

Date Formatting with CONVERT Styles​

Parsing Date Strings with CONVERT​

CAST vs CONVERT in SQL Server​

MySQL-Specific Conversions​

MySQL CONVERT Function​

Implicit vs Explicit Conversion​

Where Implicit Conversion Happens​

When Implicit Conversion Goes Wrong​

Explicit Is Always Better​

Common Conversion Scenarios​

Scenario 1: Cleaning Imported CSV Data​

Scenario 2: Building Display Strings​

Scenario 3: Grouping by Date Components​

Scenario 4: Conditional Type Conversion​

Scenario 5: Safe Conversion Functions​

Common Conversion Pitfalls​

Pitfall 1: CAST Failure Crashes the Entire Query​

Pitfall 2: Precision Loss When Casting Decimals​

Pitfall 3: Date Format Ambiguity​

Pitfall 4: Boolean Conversion Inconsistency​

Pitfall 5: NULL Handling in CAST​

Pitfall 6: Casting in WHERE Prevents Index Usage​

Type Conversion Compatibility Reference​

Quick Reference​

Summary​

Table of Contents

The Sample Data

CAST: The SQL Standard

Syntax

String to Number

Number to String

String to Date

Timestamp to Date (Stripping Time)

Integer to Decimal (Fixing Integer Division)

PostgreSQL Shorthand: :: Operator

CONVERT: SQL Server's Extended Conversion

Syntax

Date Formatting with CONVERT Styles

Parsing Date Strings with CONVERT

CAST vs CONVERT in SQL Server

MySQL-Specific Conversions

MySQL CONVERT Function

Implicit vs Explicit Conversion

Where Implicit Conversion Happens

When Implicit Conversion Goes Wrong

Explicit Is Always Better

Common Conversion Scenarios

Scenario 1: Cleaning Imported CSV Data

Scenario 2: Building Display Strings

Scenario 3: Grouping by Date Components

Scenario 4: Conditional Type Conversion

Scenario 5: Safe Conversion Functions

Common Conversion Pitfalls

Pitfall 1: CAST Failure Crashes the Entire Query

Pitfall 2: Precision Loss When Casting Decimals

Pitfall 3: Date Format Ambiguity

Pitfall 4: Boolean Conversion Inconsistency

Pitfall 5: NULL Handling in CAST

Pitfall 6: Casting in WHERE Prevents Index Usage

Type Conversion Compatibility Reference

Quick Reference

Summary