CAST in SQL — type conversion explained

Why CAST matters in analyst work

Type coercion is the boring-looking topic that quietly blows up about a third of analyst dashboards. You write SELECT conversions / visits AS conversion_rate, ship it, and the chart shows a flat zero for every row. The query is correct. The schema is the problem: both columns are integers, the result is integer-divided, and anything below 1.0 truncates to 0. The fix is a one-token addition — a CAST — and the entire dashboard wakes up.

That is the single most important reason analysts care about CAST. The second is parsing strings that arrived as dates from a CSV upload or a third-party API. The third is making concatenation work when you want to glue an order ID onto a label. Interview panels at companies like Stripe, Airbnb, and DoorDash lean on these traps because they separate the candidate who has shipped a dashboard from the one who has only read a textbook.

Syntax across dialects

The ANSI-standard form is CAST(expression AS target_type). It works in every major engine — Postgres, MySQL, SQL Server, Snowflake, BigQuery, Redshift, ClickHouse, DuckDB. Postgres adds a shorthand expression::target_type that is convenient but non-portable. SQL Server keeps the older CONVERT(type, expression, style) for backward compatibility, and MySQL has CONVERT(expression, type) with a different argument order. When in doubt, write CAST. It is the answer that does not break when your team migrates from Redshift to Snowflake.

-- Standard SQL, portable everywhere
CAST(expression AS target_type)

-- Postgres shorthand (also works in DuckDB and Redshift)
expression::target_type

-- Common examples
SELECT CAST('42' AS INTEGER);          -- 42
SELECT CAST(3.14 AS INTEGER);          -- 3 (truncated, NOT rounded)
SELECT CAST(42 AS VARCHAR);            -- '42'
SELECT CAST('2026-03-15' AS DATE);     -- 2026-03-15
SELECT '42'::INTEGER;                  -- 42 (Postgres shorthand)
SELECT '2026-03-15'::DATE;             -- 2026-03-15

The integer division trap

This is the question that decides whether an analyst candidate makes it past the SQL screen. The setup is innocent: count successes, divide by total, multiply by 100. The execution is wrong because integer divided by integer stays integer.

-- Wrong: integer division silently truncates
SELECT 7 / 2;                                  -- 3, NOT 3.5
SELECT COUNT(*) / COUNT(DISTINCT user_id);     -- truncates to 0 OR 1

-- Right: cast at least one side to numeric
SELECT CAST(7 AS NUMERIC) / 2;                            -- 3.5
SELECT 7::NUMERIC / 2;                                    -- 3.5
SELECT COUNT(*)::NUMERIC / COUNT(DISTINCT user_id);       -- 3.5

-- Or multiply by 1.0 to force float
SELECT 7 * 1.0 / 2;                                       -- 3.5

The rule worth memorising: if either operand is floating-point or numeric, the result is floating-point. You only need to cast one side. That is why 1.0 * is a popular shorthand — it costs you one character to defuse the trap.

Percentages are the canonical place this breaks. Here is the version you want to copy into every funnel query you write:

SELECT
    status,
    COUNT(*) AS cnt,
    ROUND(
        COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100, 1
    ) AS pct
FROM orders
GROUP BY status;

Without the ::NUMERIC cast, COUNT/SUM returns 0 for every row because both sides are bigint, and 0/n truncates. With it, you get the percentage breakdown you actually wanted.

String, number, and date conversions

The three directions are: parse a string into a typed value, format a typed value as a string, and reinterpret one numeric type as another. Each has its own footgun.

String to number works for clean inputs and throws on dirty ones. CAST('42' AS INTEGER) returns 42. CAST('abc' AS INTEGER) throws and tanks your query. If you do not control upstream data, gate the cast behind a regex or use TRY_CAST (Snowflake, BigQuery, SQL Server, Postgres 16+).

-- Defensive parsing: never let dirty data kill the query
SELECT
    CASE
        WHEN value ~ '^\d+$' THEN CAST(value AS INTEGER)
        ELSE NULL
    END AS safe_value
FROM raw_data;

-- Snowflake / BigQuery / SQL Server / PG 16+
SELECT TRY_CAST(value AS INTEGER) AS safe_value FROM raw_data;

Number to string is needed for concatenation. Postgres will accept 'Order #' || order_id because it implicitly casts the integer, but MySQL will not. The portable form is 'Order #' || CAST(order_id AS VARCHAR) or, in MySQL, CONCAT('Order #', order_id). For formatted numbers — thousand separators, fixed decimal places — use TO_CHAR in Postgres or FORMAT in SQL Server.

String to date is where dialects diverge most. ISO format YYYY-MM-DD is universally accepted by every major engine. Anything else — European DD.MM.YYYY, US MM/DD/YYYY, mixed timestamps — needs an explicit format string.

-- Universal ISO format works everywhere
SELECT CAST('2026-03-15' AS DATE);                    -- 2026-03-15
SELECT CAST('2026-03-15 14:30:00' AS TIMESTAMP);

-- Postgres / Oracle / Snowflake: TO_DATE with format mask
SELECT TO_DATE('15.03.2026', 'DD.MM.YYYY');           -- 2026-03-15
SELECT TO_TIMESTAMP('15/03/26 14:30', 'DD/MM/YY HH24:MI');

-- MySQL: STR_TO_DATE with %-tokens
SELECT STR_TO_DATE('15.03.2026', '%d.%m.%Y');

-- BigQuery: PARSE_DATE
SELECT PARSE_DATE('%d.%m.%Y', '15.03.2026');

For the reverse direction — date to string — Postgres uses TO_CHAR, MySQL uses DATE_FORMAT, SQL Server uses FORMAT or the legacy CONVERT with style codes, and BigQuery uses FORMAT_DATE. The format tokens differ. Postgres uses 'YYYY-MM-DD', MySQL uses '%Y-%m-%d'. It is irritating but not deep.

CAST vs CONVERT vs the double colon

The interview question "what is the difference between CAST and CONVERT" is mostly about portability and ergonomics. Here is the comparison you should be able to draw on a whiteboard.

The actionable takeaway: write CAST in code that travels, use :: for ad-hoc Postgres queries where every character counts, and reach for TRY_CAST or SAFE_CAST whenever the input might be dirty. SQL Server's CONVERT(VARCHAR, GETDATE(), 104) style codes are a relic — they work, but FORMAT is more readable.

Worked examples from real dashboards

Two patterns you will hit in your first month on a data team. Each is a place where the wrong cast either silently corrupts the output or throws at runtime.

Daily conversion rate. A funnel with visits and purchases, computed as a percentage, grouped by day. The combination of NULLIF for safe division, ::NUMERIC for fractional math, and ROUND for human-readable output is the canonical recipe.

SELECT
    DATE(created_at) AS day,
    COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END)::NUMERIC
        / NULLIF(COUNT(DISTINCT CASE WHEN event = 'visit' THEN user_id END), 0)
        * 100 AS conversion_pct
FROM events
GROUP BY DATE(created_at)
ORDER BY day;

Monthly cohort from a string date column. When upstream pipelines deliver event_date as text — common with CSV uploads from finance or ops — you parse, truncate, and group. This is also how you discover that one row has the value '2026-02-30' and learn to use TRY_CAST.

SELECT
    TO_CHAR(CAST(date_str AS DATE), 'YYYY-MM') AS month,
    COUNT(*) AS events
FROM raw_logs
GROUP BY 1
ORDER BY 1;

Common pitfalls

The first pitfall is integer division silently truncating to zero. This is the most common bug in dashboards owned by analysts who have never worked with strongly typed languages. The fix is to cast one side of every ratio to NUMERIC or FLOAT, every time, without thinking about it. Build the muscle memory: if there is a slash in your SELECT clause, there is a cast nearby.

The second pitfall is CAST truncating instead of rounding on floating-point inputs. CAST(3.999 AS INTEGER) is 3, not 4. Interviewers love this one because it filters out candidates who have only worked with implicit conversions in Python or JavaScript. If you want rounding, apply ROUND first and then cast: CAST(ROUND(3.999) AS INTEGER).

The third pitfall is letting dirty strings reach a hard CAST. A single non-numeric value in a text column will fail the entire query when you CAST(col AS INTEGER). In production pipelines, wrap the cast in a regex guard or use the dialect's safe variant — TRY_CAST in Snowflake, SQL Server, and Postgres 16+; SAFE_CAST in BigQuery. Errors that return NULL are almost always preferable to errors that return nothing at all.

The fourth pitfall is implicit coercion that works locally and breaks in production. Postgres will happily evaluate WHERE age > '25' because it coerces the string to an integer. The same query against MySQL with strict mode, or against BigQuery, may either error or, worse, give a different result because the comparison goes the other direction (integer to string, lexicographic). Always cast both sides of a comparison to the same type explicitly. This is the single most common reason a query reviewed in staging behaves differently after migration.

The fifth pitfall is timezone collapse on CAST(timestamp AS DATE). The cast uses the session's timezone, which can be UTC on the warehouse but Pacific on a BI tool. Two users querying the same view get different cohort assignments near midnight. The defensive form is explicit: CAST(event_at AT TIME ZONE 'UTC' AS DATE) in Postgres, or DATE(event_at, 'UTC') in BigQuery.

Interview questions

What does SELECT 5 / 2 return in Postgres? It returns 2, not 2.5. Both operands are integers, so the result is integer-divided. To get 2.5 you would write 5::NUMERIC / 2, 5 * 1.0 / 2, or CAST(5 AS NUMERIC) / 2.

Does CAST round or truncate? It truncates toward zero. CAST(3.9 AS INTEGER) is 3, CAST(-3.9 AS INTEGER) is -3 in most dialects. For rounding, apply ROUND() first.

What is the difference between CAST and the :: operator? They do the same thing in Postgres. :: is a Postgres-specific shorthand also adopted by Snowflake, Redshift, and DuckDB. CAST is ANSI standard and works in every major engine.

How do you safely cast a string column that might contain garbage? Three options: a regex guard inside CASE WHEN col ~ '^\d+$' THEN col::INT END; TRY_CAST in Snowflake/SQL Server/Postgres 16+; SAFE_CAST in BigQuery. All three return NULL on failure instead of throwing.

Can you CAST NULL? Yes. CAST(NULL AS INTEGER) returns a typed NULL. This matters in UNION queries where each column across branches must have the same type — write CAST(NULL AS INTEGER) AS user_id to keep types aligned.

If you want to drill questions like these every day until the trap-spotting becomes automatic, NAILDD is launching with 500+ SQL problems built around exactly this pattern of casting, conversion, and dialect-specific gotchas.

Related reading

• CASE WHEN in SQL — full guide
• COALESCE in SQL — handling NULL safely
• NULL in SQL — the three-valued logic guide
• SQL window functions interview questions
• SQL antipatterns to avoid

FAQ

Does CAST round or truncate floating-point values?

CAST truncates toward zero. CAST(3.9 AS INTEGER) returns 3, and CAST(-3.9 AS INTEGER) typically returns -3. For rounding behaviour, wrap with ROUND() first: CAST(ROUND(3.9) AS INTEGER) returns 4. This is one of the most common interview gotchas because candidates with a Python or JavaScript background expect implicit rounding.

Can I CAST a NULL value?

Yes, and you should. CAST(NULL AS INTEGER) returns a typed NULL, useful inside UNION queries where every branch must agree on column types. If branch A has a user_id column and branch B does not, write CAST(NULL AS BIGINT) AS user_id in branch B to keep types aligned.

What is the difference between CAST and TO_CHAR or TO_DATE?

CAST is general-purpose and dialect-portable, but it does not accept a format string. For dates and numbers in non-ISO formats, you need TO_CHAR and TO_DATE (Postgres, Oracle, Snowflake), DATE_FORMAT and STR_TO_DATE (MySQL), or PARSE_DATE and FORMAT_DATE (BigQuery).

Is :: faster than CAST in Postgres?

No. They are syntactic siblings and produce identical query plans. The Postgres parser rewrites x::type to CAST(x AS type) before planning. Use :: for brevity, CAST for portability.

What is TRY_CAST and where can I use it?

TRY_CAST returns NULL instead of throwing when the conversion fails. It is supported in SQL Server, Snowflake, and Postgres 16+. BigQuery has the equivalent SAFE_CAST. Always prefer the safe variant when parsing user-supplied or third-party data — silent NULLs are easier to debug than failed queries.

Why does my percentage column show all zeros?

Integer division. Both your numerator and denominator are integer types (typically COUNT(*) or SUM(int_col)), and SQL truncates the result before multiplying. Cast one side to NUMERIC: COUNT(*)::NUMERIC / total_count. This is by far the most common bug in analyst dashboards.