Gowtham Potureddi

Posted on May 23

SQL CAST, CONVERT & Type Coercion: Safe Conversions for Data Engineers

#python #sql #interview #dataengineering

sql cast is the ANSI-standard way to convert one type to another (CAST(expr AS type)), sql convert is the SQL Server / MySQL alternative (with extra format-string powers on dates), and implicit type coercion is what the engine does silently when two types meet — INTEGER + NUMERIC, VARCHAR = DATE, '2026-05-22' < '2026-06-01'. These three behaviours answer the bulk of the sql interview questions in the "what type is this expression?" cluster, and getting the cast vs convert in sql distinction right — together with the safe-parsing operators (TRY_CAST, TRY_CONVERT, SAFE_CAST) — separates candidates who pass screening from candidates who don't.

This guide walks through every clause in the sql cast convert family that reviewers love to test in data engineering interview questions: the explicit-vs-implicit conversion mental model, the CAST(expr AS type) signature and how Postgres extends it to ::type, the SQL Server CONVERT(type, expr, style) signature with its format-string powers on dates, the safe-parsing operators (TRY_CAST, TRY_CONVERT, SAFE_CAST) that swallow conversion errors and return NULL instead, dialect-specific type-coercion rules that silently change query results, and the seven gotchas (lossy casts, locale-dependent parsing, comparison coercion) that fail most candidates. Every section ends as sql interview questions with answers: a runnable PostgreSQL query, a traced execution, an output table, and a concept-by-concept why this works breakdown — the exact shape sql for data engineers rounds reward when type casting in sql comes up.

When you want hands-on reps immediately after reading, browse type-casting SQL practice →, drill the aggregation SQL lane →, sharpen date-functions SQL drills →, rehearse string-manipulation problems →, or widen coverage on the full SQL practice library →.

On this page

Why CAST and CONVERT matter in data engineering interviews
Explicit vs implicit type coercion — the silent-result-changer
CAST(expr AS type) — the ANSI-standard cast
CONVERT(type, expr, style) — SQL Server's superpower on dates
TRY_CAST, TRY_CONVERT, SAFE_CAST — error-swallowing safe casts
Numeric, string, and date conversion patterns
Cast gotchas — lossy conversions, locale, comparison coercion
Choosing the right conversion (cheat sheet)
Frequently asked questions
Practice on PipeCode

1. Why CAST and CONVERT matter in data engineering interviews

Two families — explicit conversion and implicit coercion — that's the whole interview surface

The one-sentence invariant: explicit conversion uses CAST or CONVERT to spell out a type change you authored; implicit coercion is what the engine does silently when two types collide in an expression, and getting either wrong silently changes query results without raising an error. Once you internalise that distinction, every prompt in the type casting in sql family becomes "name the conversion explicitly so the reviewer can see your intent."

The two function families and their signatures.

CAST(expr AS target_type) — ANSI standard; supported by every dialect; PostgreSQL also accepts the shorthand expr::target_type.
CONVERT(target_type, expr [, style]) — SQL Server / MySQL variant; SQL Server's extra style argument is the killer feature for date formatting; in MySQL, argument order is CONVERT(expr, target_type) (reversed!).
Safe variants — TRY_CAST / TRY_CONVERT (SQL Server), SAFE_CAST (BigQuery), TRY_TO_NUMBER (Snowflake) — return NULL instead of raising when the conversion fails.

The three reasons a data engineer reaches for an explicit cast.

Force a desired output type — CAST(SUM(amount)::numeric AS DECIMAL(12,2)) for currency-formatted totals.
Avoid integer-division surprises — CAST(num AS NUMERIC) / denom instead of relying on implicit widening.
Parse string inputs — '2026-05-22'::date, CAST('42' AS INTEGER), TRY_CAST('hello' AS INTEGER) for robust ingestion of CSVs and APIs.

Why interviewers love type conversion.

Implicit coercion silently changes results — '10' < '2' evaluates to true (lexicographic) while 10 < 2 is false; mismatched types in a join key can return zero rows.
Dialect divergence is huge — CAST works everywhere; CONVERT differs across SQL Server and MySQL; safe-cast operators are dialect-specific.
It's where data-quality bugs hide — bad CSV imports, locale-dependent date parsing, lossy numeric casts.
The cast vs convert in sql interview question is one of the most-asked SQL-Server-flavour questions in a panel.

What interviewers listen for.

Do you prefer explicit casts over implicit coercion in any non-trivial expression? — senior signal.
Do you reach for TRY_CAST / SAFE_CAST when parsing user-supplied strings instead of letting the engine error out? — production discipline.
Do you mention lossy-cast risks when converting NUMERIC(20,4) to INTEGER or TIMESTAMPTZ to TIMESTAMP? — bonus points.
Do you know the dialect-specific quoting (::type in Postgres, [type] in SQL Server) and the argument order in CONVERT? — basic-but-tested fluency.

Worked example — safe-parse a CSV column with explicit cast

Detailed explanation. Real-world CSV imports often arrive with numeric or date values stored as strings. The data engineer's job is to convert them to the right type without letting bad rows blow up the whole load. The portable pattern is a TRY_CAST (or SAFE_CAST) that returns NULL for unparseable rows, paired with a downstream WHERE filter to surface the dirty rows for review.

Question. From raw_orders(order_id, amount_str, order_date_str) (a staging table with string columns from a CSV), return a cleaned result with numeric amounts and parsed dates, flagging rows that fail to parse.

Input. raw_orders slice.

order_id	amount_str	order_date_str
1	100.50	2026-05-22
2	abc	2026-05-23
3	200.00	not-a-date
4	350	2026-05-24

Code (PostgreSQL).

SELECT order_id,
       CASE
           WHEN amount_str ~ '^[0-9]+(\.[0-9]+)?$'
           THEN amount_str::numeric
       END AS amount,
       CASE
           WHEN order_date_str ~ '^\d{4}-\d{2}-\d{2}$'
           THEN order_date_str::date
       END AS order_date,
       (amount_str !~ '^[0-9]+(\.[0-9]+)?$'
        OR order_date_str !~ '^\d{4}-\d{2}-\d{2}$') AS has_parse_error
FROM raw_orders
ORDER BY order_id;

Equivalent in SQL Server using TRY_CAST.

SELECT order_id,
       TRY_CAST(amount_str    AS DECIMAL(12,2)) AS amount,
       TRY_CAST(order_date_str AS DATE)         AS order_date,
       CASE
           WHEN TRY_CAST(amount_str    AS DECIMAL(12,2)) IS NULL
             OR TRY_CAST(order_date_str AS DATE)         IS NULL
           THEN 1 ELSE 0
       END AS has_parse_error
FROM raw_orders
ORDER BY order_id;

Step-by-step explanation.

The regex (Postgres) or TRY_CAST (SQL Server) gates each cast — only well-formed strings convert; the rest become NULL.
has_parse_error flags rows whose amount_str or order_date_str failed to parse.
Downstream the loader can WHERE has_parse_error = false for the clean rows and WHERE has_parse_error = true for an error queue.
The casts are explicit (::numeric, ::date, AS DATE) — no implicit coercion involved.

Output.

order_id	amount	order_date	has_parse_error
1	100.50	2026-05-22	false
2	NULL	2026-05-23	true
3	200.00	NULL	true
4	350.00	2026-05-24	false

Rule of thumb: never let bad CSV rows blow up the entire load — wrap risky casts in TRY_CAST / SAFE_CAST and flag bad rows for triage; the clean ones still land.

SQL
Topic — type-casting
Type casting SQL drills

Practice →

SQL
Topic — string-manipulation
String parsing SQL practice

Practice →

2. Explicit vs implicit type coercion — the silent-result-changer

`implicit type conversion sql` — what the engine does when you don't ask

Every dialect has implicit-coercion rules for what happens when two types meet in an expression: INTEGER + NUMERIC, VARCHAR = INTEGER, DATE < TIMESTAMP. The rules differ slightly across engines, but the consequences are universal — silently wrong results, broken indexes, surprising sort orders, and rows that mysteriously fail to match in joins.

Implicit type coercion — the engine silently changes a type.

Numeric widening — INTEGER + NUMERIC widens INTEGER to NUMERIC; INTEGER + FLOAT widens to FLOAT. Generally safe.
String parsing in comparisons — INTEGER = '42' typically casts the string to integer; '10' < '2' is the famous trap (string-to-string is lexicographic, NOT numeric).
Date / timestamp — comparing DATE to TIMESTAMP widens the DATE to the start-of-day timestamp; comparing DATE to a string parses the string as a date in dialect-specific locale.
NULL propagation — any operation involving NULL returns NULL regardless of types.

The three reasons implicit coercion is dangerous in production.

It hides intent — a reader of your code cannot tell which conversion fired without consulting the dialect manual.
It defeats indexes — WHERE int_col = '42' may NOT use the int index on int_col because the engine sees a string comparison and casts each row.
It silently changes results — '10' < '2' is true; that bug is invisible until production data triggers it.

Why interviewers love testing this.

The classic prompt: "what does SELECT '10' < '2' return?" — most candidates say false; the truthful answer in lexicographic compare is true.
The fix is explicit casts: '10'::int < '2'::int returns false.
It surfaces whether the candidate understands what their queries do at the type level.

Explicit conversion — the senior default.

Spells out intent — CAST(amount AS NUMERIC) tells reviewers and future readers what type you intend.
Avoids the integer-division trap — CAST(num AS NUMERIC) / denom is fractional; num / denom is integer-truncated if both are integers.
Keeps indexes usable — WHERE int_col = CAST('42' AS INTEGER) plans like a clean integer compare.
Predictable across dialects — CAST(x AS NUMERIC) behaves the same on every engine; implicit coercion rules differ.

The integer-division trap — every dialect, every interview.

5 / 2 returns 2 in Postgres / SQL Server / Oracle (both operands INTEGER → integer division).
5 / 2.0 returns 2.5 (right side NUMERIC → widening).
CAST(5 AS NUMERIC) / 2 returns 2.5 — explicit cast on either side fixes it.
MySQL is the exception — divides as floating point even with two integers (5 / 2 = 2.5).
The interview answer — always cast the numerator (or denominator) when you want a fractional result.

Comparison coercion — the silent join-failure trap.

WHERE varchar_col = int_literal — most engines cast varchar_col to integer per row, which defeats the B-tree index on varchar_col and forces a table scan.
WHERE date_col = '2026-05-22' — usually fine; engine parses the string as a date once.
JOIN ON a.varchar_col = b.int_col — silently casts one side per row; slow and brittle.
Fix — make the join key types match at the schema layer; never join across types.

Explicit-cast discipline rules of thumb.

In every non-trivial expression — cast both sides of an arithmetic / comparison if the types are not obvious from the schema.
When parsing strings — always CAST or TRY_CAST; never compare a VARCHAR literal directly to a numeric column.
When dividing — cast at least one side to NUMERIC / DECIMAL / FLOAT.
When joining — confirm both keys are the same type; otherwise CAST upstream in a CTE.

SQL
Topic — type-casting
Implicit vs explicit cast drills

Practice →

SQL
Topic — aggregation
Integer-division SQL practice

Practice →

3. CAST(expr AS type) — the ANSI-standard cast

`cast in sql` — the conversion every dialect understands

CAST(expr AS target_type) is the ANSI-standard explicit conversion operator. Every major dialect supports it. PostgreSQL adds the expr::target_type shorthand. Use CAST when you want one canonical operator that travels across engines; use :: when you're in a Postgres-only codebase and want compact code.

CAST(expr AS target_type) — the universal signature.

Signature — CAST(expression AS type_name).
Returns — expression converted to target_type, or raises an error if the conversion is illegal.
Supported by — PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery, SQLite, every dialect that implements ANSI SQL.
Best for — portable conversion code that must compile on every engine.

PostgreSQL ::type shorthand.

Signature — expression::target_type.
Identical semantics to CAST — same conversion rules, same errors.
More compact — amount::numeric reads better than CAST(amount AS NUMERIC) in chained expressions.
PostgreSQL-only — not portable to MySQL or SQL Server.
Common interview gotcha — 2026-05-22::date parses without quotes in Postgres because date literals are special.

Common CAST patterns by target type.

CAST(x AS INTEGER) — truncate toward zero (Postgres / Oracle) or round-half-to-even (some dialects); errors on overflow.
CAST(x AS NUMERIC(p,s)) — fixed-precision decimal; rounds the fractional part to scale s.
CAST(x AS DECIMAL(p,s)) — alias for NUMERIC(p,s) in most dialects.
CAST(x AS VARCHAR(n)) — string representation; truncates or errors if n is too small.
CAST(x AS DATE) — drops the time component if x is TIMESTAMP; parses a string if x is VARCHAR.
CAST(x AS TIMESTAMP) — adds midnight if x is DATE; parses a string if x is VARCHAR.
CAST(x AS BOOLEAN) — 'true' / 'false' strings, 1 / 0 integers; dialect-specific.

The integer-division fix — the most-asked use case.

-- Wrong: 5/2 = 2 (integer division)
SELECT  5 / 2 AS bad;
-- Right: cast either side to NUMERIC for fractional result
SELECT CAST(5 AS NUMERIC) / 2     AS good_postgres;
SELECT       5::numeric / 2        AS good_postgres_shorthand;
SELECT       5 / 2.0               AS good_implicit_widening;

Casting strings to numbers — the parsing path.

CAST('42' AS INTEGER) returns 42.
CAST('42.5' AS NUMERIC) returns 42.5.
CAST('abc' AS INTEGER) errors — that's why TRY_CAST exists (§5).
Leading / trailing whitespace — most dialects tolerate it; some don't.
Locale-sensitive — CAST('1,234.56' AS NUMERIC) errors in Postgres; works in some MySQL configurations.

Casting numbers to strings.

CAST(123 AS VARCHAR) returns '123'.
CAST(123.456 AS VARCHAR) returns '123.456' (or '123.46' depending on precision settings).
For controlled formatting — use TO_CHAR(n, '999,999.99') (Postgres / Oracle) or FORMAT(n, 'N2') (SQL Server) instead of CAST.

Casting dates and timestamps.

CAST('2026-05-22' AS DATE) parses the string as a date in ISO format (every dialect).
CAST('2026-05-22' AS TIMESTAMP) parses as 2026-05-22 00:00:00.
CAST(ts AS DATE) drops the time component, keeps the date.
For non-ISO strings — use TO_DATE(string, pattern) (Postgres) or CONVERT(DATE, string, style_code) (SQL Server).

SQL interview question — average order value per region, two-decimal display

Assume orders(region, amount) where amount is INTEGER (storing currency in cents). Return per region: total orders, total revenue (cast to NUMERIC for two-decimal display), and average order value to 2 decimal places.

Solution Using CAST + ROUND

Detailed explanation. This is the classic integer-division fix combined with rounding. The naive AVG(amount) on integer columns returns an integer in some dialects (truncated mean); casting to NUMERIC before averaging gives the fractional answer, and ROUND(…, 2) clamps to currency-display precision.

Code (PostgreSQL).

SELECT region,
       COUNT(*)                              AS total_orders,
       CAST(SUM(amount) AS NUMERIC(12,2))    AS total_revenue,
       ROUND(AVG(amount)::numeric, 2)        AS avg_order_value
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

Equivalent in SQL Server.

SELECT region,
       COUNT(*)                                       AS total_orders,
       CAST(SUM(amount) AS DECIMAL(12,2))             AS total_revenue,
       ROUND(CAST(AVG(amount) AS DECIMAL(12,2)), 2)   AS avg_order_value
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

Step-by-step trace.

step	relation	outcome
1	Scan `orders`	every row read
2	`GROUP BY region`	one row per region
3	`COUNT(*)`	order count per region
4	`CAST(SUM(amount) AS NUMERIC(12,2))`	total revenue widened to fixed-precision decimal
5	`AVG(amount)::numeric`	average widened from INTEGER to NUMERIC to avoid integer-mean truncation
6	`ROUND(…, 2)`	clamp to currency-display precision

Output:

region	total_orders	total_revenue	avg_order_value
US	142	12,500.00	88.03
EU	98	8,750.00	89.29
APAC	64	5,400.00	84.38

Why this works — concept by concept:

CAST(SUM(amount) AS NUMERIC(12,2)) — promotes the integer sum to fixed-precision decimal so the display shows two decimals consistently.
AVG(amount)::numeric — without the cast, AVG on an integer column may return an integer (MySQL, older Postgres) which truncates the mean; explicit widening preserves fractional precision.
ROUND(…, 2) — clamps to currency-display precision; without this the average could show many trailing digits.
Postgres ::numeric shorthand — same semantics as CAST(… AS NUMERIC); just terser inside a chained expression.
No implicit coercion involved — every conversion is explicit; the reviewer can see exactly what type each output column will be.
Cost — single hash aggregate; the casts are constant-per-row overhead, negligible compared to the scan.

SQL
Topic — type-casting
CAST SQL drills

Practice →

SQL
Topic — aggregation
CAST + aggregation patterns

Practice →

4. CONVERT(type, expr, style) — SQL Server's superpower on dates

`convert in sql` — SQL Server's three-argument form with format styles

sql convert is the SQL Server / MySQL alternative to sql cast. The signature differs across the two dialects (SQL Server: CONVERT(type, expr, style); MySQL: CONVERT(expr, type) — reversed!), and SQL Server's third style argument is the killer feature for date-to-string formatting. The sql convert operator is the second half of the cast vs convert in sql interview comparison.

SQL Server CONVERT(type, expr [, style]).

Signature — CONVERT(target_type, expression, style_code).
type comes first (unlike CAST(expr AS type)).
style is the magic third argument — controls date-to-string and string-to-date formatting.
Performance note — CONVERT is slightly faster than FORMAT in SQL Server because it uses C-level formatters, not .NET.

The SQL Server style codes for dates — the high-value cheat.

Style	Output format	Example
`101`	`mm/dd/yyyy` (US)	`05/22/2026`
`103`	`dd/mm/yyyy` (UK)	`22/05/2026`
`112`	`yyyymmdd`	`20260522`
`120`	`yyyy-mm-dd hh:mi:ss`	`2026-05-22 14:30:00`
`121`	`yyyy-mm-dd hh:mi:ss.mmm`	`2026-05-22 14:30:00.000`
`23`	`yyyy-mm-dd` (ISO date)	`2026-05-22`
`126`	`yyyy-mm-ddThh:mi:ss.mmm` (ISO 8601)	`2026-05-22T14:30:00.000`

Style code 23 is the most-used in interview answers — clean ISO date.
Style code 120 is the most-used for log timestamps.
Style code 126 is the right choice for any ISO-8601 / API payload formatting.

CAST vs CONVERT in SQL Server — the interview-canonical comparison.

Both work for the same conversion intent — CAST is ANSI-portable; CONVERT is SQL Server-specific.
CONVERT has the style argument — CAST does not.
CAST is recommended for new code unless you need the style argument.
CONVERT is slightly faster in benchmarks but the difference is negligible at most query scales.

MySQL CONVERT(expr, type) — argument order reversed.

Signature — CONVERT(expression, target_type) — expr first, type second.
No style argument in MySQL.
Identical semantics to CAST(expr AS type) for the same conversion.
Common bug — copying SQL Server CONVERT(type, expr) into MySQL silently parses wrong.

Worked example — format dates for a UK-locale report.

-- SQL Server
SELECT order_id,
       CONVERT(VARCHAR, order_date, 103) AS uk_date_format,
       CONVERT(VARCHAR, order_date, 23)  AS iso_date_format,
       CONVERT(VARCHAR, order_ts,   120) AS log_timestamp
FROM orders;

The style argument's quiet power — non-ISO parsing.

CONVERT(DATE, '22/05/2026', 103) — parses dd/mm/yyyy UK-format string into a DATE.
CONVERT(DATE, '05/22/2026', 101) — parses US mm/dd/yyyy.
Without the style — SQL Server tries to parse with the session's regional setting; that's the locale-bug source.
Always pass a style when parsing strings to dates in SQL Server.

Choosing between CAST, CONVERT, and FORMAT in SQL Server.

CAST — portable, no style argument; first choice for non-date conversions.
CONVERT — SQL Server-specific; reach for it when you need the style argument on dates.
FORMAT — .NET-style formatting (yyyy-MM-dd, MMMM dd, yyyy); most readable but slowest.
Decision rule — for currency display, use FORMAT (readability); for date display at scale, use CONVERT (performance); for type conversion in general, use CAST (portability).

SQL
Topic — type-casting
CONVERT SQL drills

Practice →

SQL
Topic — date-functions
Date formatting SQL practice

Practice →

5. TRY_CAST, TRY_CONVERT, SAFE_CAST — error-swallowing safe casts

`try_cast in sql` — return NULL instead of raising on bad input

The safe-cast family — TRY_CAST (also written try cast in informal prose), TRY_CONVERT, SAFE_CAST — does one job: when the conversion would fail (CAST('abc' AS INTEGER) raises an error), it returns NULL instead. This single behaviour change makes the safe-cast operators the right default for any column you don't trust to be perfectly clean — CSV imports, JSON payloads, user input, scraped data. The try cast in sql search trend is one of the fastest-growing data-engineering queries because every staging-table loader needs it.

SQL Server TRY_CAST and TRY_CONVERT.

Signature — TRY_CAST(expression AS target_type), TRY_CONVERT(target_type, expression [, style]).
Returns — the converted value on success; NULL on failure (instead of raising).
Supported since — SQL Server 2012.
Common use — parsing string columns from CSVs / staging tables; filtering bad rows downstream.

BigQuery SAFE_CAST.

Signature — SAFE_CAST(expression AS target_type).
Returns — converted value on success; NULL on failure.
Semantics match TRY_CAST.
SAFE. prefix — also SAFE.PARSE_DATE, SAFE.PARSE_TIMESTAMP, etc., for date parsing.

Snowflake TRY_TO_* family.

TRY_TO_NUMBER(string) — safe parse to number; returns NULL on failure.
TRY_TO_DATE(string [, format]) — safe parse to date.
TRY_TO_TIMESTAMP(string [, format]) — safe parse to timestamp.
TRY_CAST(expr AS type) — generic safe cast.

PostgreSQL — no built-in TRY_CAST (yet).

Pre-Postgres 16 — must use a CASE WHEN regex filter or write a custom function.
Postgres 16+ — CAST(expr AS type ERROR ON NULL) is the closest standard form; some extensions provide TRY_CAST.
The portable workaround — wrap the cast in CASE:

  CASE WHEN col ~ '^[0-9]+$'
       THEN col::int
       ELSE NULL
  END

Custom function alternative:

  CREATE FUNCTION try_int(text) RETURNS int AS $$
  BEGIN
      RETURN $1::int;
  EXCEPTION WHEN OTHERS THEN
      RETURN NULL;
  END;
  $$ LANGUAGE plpgsql IMMUTABLE STRICT;

MySQL — partial support.

No TRY_CAST keyword — but MySQL has very lenient implicit coercion (returns 0 or NULL instead of erroring on bad casts), which produces a similar effect.
The catch — silent 0-returns can mask bugs the way NULL does not; TRY_CAST returning NULL is preferable.

When to reach for safe casts.

Parsing staging-table string columns loaded from CSVs.
Validating user input in production queries.
Joining across heterogeneous types where one side might be malformed.
Anywhere bad data could blow up the whole batch — graceful NULL is better than a hard fail.

When NOT to use safe casts.

Trusted internal data — if the upstream pipeline guarantees clean integers, TRY_CAST masks bugs you'd want to know about.
Strict validation flows — auth tokens, IDs, etc.; you'd rather error than silently coerce to NULL.
Default-zero traps — TRY_CAST(badstring AS INT) returns NULL; followed by COALESCE(…, 0) silently swallows bad rows.

The TRY_CAST + WHERE IS NULL pattern for data quality.

-- SQL Server: surface rows that fail to parse
SELECT order_id, amount_str
FROM raw_orders
WHERE TRY_CAST(amount_str AS DECIMAL(12,2)) IS NULL
  AND amount_str IS NOT NULL;

The IS NOT NULL guard avoids reporting genuinely-null inputs as parse failures.
The output is the error queue: rows the loader could not parse.
Downstream the clean rows still load.

SQL
Topic — type-casting
TRY_CAST SQL drills

Practice →

SQL
Topic — string-manipulation
Safe parsing SQL library

Practice →

6. Numeric, string, and date conversion patterns

The three conversion paths every data engineer touches

Most cast / convert work in real pipelines falls into three buckets: numeric ↔ string (load CSV columns, format reports), string ↔ date (parse ingest dates, format display dates), and numeric ↔ numeric (integer ↔ decimal ↔ float for precision and division correctness).

Numeric → string.

CAST(123 AS VARCHAR) returns '123' — basic form; no formatting control.
TO_CHAR(123.456, '999,999.99') (Postgres / Oracle) → ' 123.46' — controlled formatting with thousands separator.
FORMAT(123.456, 'N2') (SQL Server) → '123.46' — .NET-style.
Use case — currency display, dashboard tiles, export to CSV.

String → numeric.

CAST('42' AS INTEGER) — clean integer parse.
CAST('42.5' AS NUMERIC) — decimal parse.
TRY_CAST('abc' AS INTEGER) — returns NULL instead of erroring.
TO_NUMBER('1,234.56', '999,999.99') (Postgres / Oracle) — locale-aware parsing of formatted strings.
Use case — CSV ingest, API payload parsing, user input validation.

Date → string.

TO_CHAR(d, 'YYYY-MM-DD') (Postgres / Oracle) → ISO format.
DATE_FORMAT(d, '%Y-%m-%d') (MySQL).
FORMAT(d, 'yyyy-MM-dd') (SQL Server, .NET-style).
CONVERT(VARCHAR, d, 23) (SQL Server, style 23) → ISO format.
Use case — report exports, log formatting, API responses.

String → date.

CAST('2026-05-22' AS DATE) — ISO format works on every dialect.
TO_DATE('22/05/2026', 'DD/MM/YYYY') (Postgres / Oracle) — pattern-based parsing.
STR_TO_DATE('22/05/2026', '%d/%m/%Y') (MySQL).
CONVERT(DATE, '22/05/2026', 103) (SQL Server, style 103 = UK).
TRY_CAST / SAFE_CAST wraps these for safety.
Use case — CSV date columns, log-timestamp parsing.

Date / timestamp ↔ date / timestamp.

CAST(ts AS DATE) — drops the time component; keeps the calendar day.
CAST(d AS TIMESTAMP) — adds midnight; widens to timestamp.
CAST(ts AS TIMESTAMPTZ) — applies session time zone; see Blog74 §2.
AT TIME ZONE — Postgres-specific conversion between time zones; not a cast.

Numeric ↔ numeric.

CAST(int_col AS NUMERIC) — promote integer to fixed-precision decimal; safe.
CAST(numeric_col AS INTEGER) — truncate fractional part; lossy; reviewer should be told this is intentional.
CAST(float_col AS NUMERIC(p,s)) — float to decimal; can lose precision past p digits.
CAST(double_col AS REAL) — double precision to single; loses about half the precision; rarely correct.

Boolean ↔ types.

CAST(0 AS BOOLEAN) → false; CAST(1 AS BOOLEAN) → true (Postgres).
CAST('true' AS BOOLEAN) → true (string parse; Postgres accepts 't', 'true', 'yes', 'on', '1').
SQL Server has no native BOOLEAN — uses BIT (0 / 1); CAST(bit AS INT) round-trips cleanly.

The dialect-portable cast table.

Source	Target	Portable cast	Notes
`INTEGER`	`NUMERIC`	`CAST(n AS NUMERIC(12,2))`	Safe widening
`NUMERIC`	`INTEGER`	`CAST(n AS INTEGER)`	Lossy; truncates
`VARCHAR`	`INTEGER`	`CAST(s AS INTEGER)` or `TRY_CAST`	Errors on bad string
`INTEGER`	`VARCHAR`	`CAST(n AS VARCHAR(20))`	Default formatting
`VARCHAR`	`DATE`	`CAST(s AS DATE)` if ISO; `TO_DATE` otherwise	ISO `YYYY-MM-DD` is universal
`DATE`	`VARCHAR`	`TO_CHAR(d, 'YYYY-MM-DD')` / `DATE_FORMAT` / `CONVERT`	Dialect-specific patterns
`TIMESTAMP`	`DATE`	`CAST(ts AS DATE)` or `ts::date`	Drops time component
`TIMESTAMP`	`TIMESTAMPTZ`	`CAST(ts AS TIMESTAMPTZ)`	Postgres applies session TZ

SQL
Topic — type-casting
Numeric / string / date cast drills

Practice →

SQL
Topic — date-functions
Date conversion patterns

Practice →

7. Cast gotchas — lossy conversions, locale, comparison coercion

The seven bugs interviewers test most often on type conversion

Type conversion has a small surface area but a long tail of edge cases that fail candidates. These are the seven gotchas reviewers test most often when type casting in sql comes up in an interview.

Gotcha 1 — Integer division silently truncates.

The bug — SELECT 5 / 2 AS half returns 2 in every dialect except MySQL; engineers expect 2.5.
Symptom — averages, ratios, and percentages off by integer truncation; bugs hide in dashboards.
Fix — cast at least one side to NUMERIC / DECIMAL / FLOAT before dividing: CAST(5 AS NUMERIC) / 2.

Gotcha 2 — CAST(string AS NUMERIC) is locale-sensitive on some engines.

The bug — CAST('1,234.56' AS NUMERIC) errors in Postgres (comma is not the decimal separator); works in some MySQL configurations.
Symptom — CSV imports from European locales fail or get wrong values.
Fix — use TO_NUMBER(string, pattern) (Postgres / Oracle) with the appropriate locale pattern, or pre-clean the string with REPLACE.

Gotcha 3 — Lossy casts truncate without warning.

The bug — CAST(123.789 AS INTEGER) returns 123 (truncated); CAST(NUMERIC(20,4) AS NUMERIC(10,2)) may round or error depending on the value.
Symptom — currency totals off by sub-cent rounding; counts inflated by truncation.
Fix — be explicit about rounding intent — CAST(ROUND(n, 0) AS INTEGER) or ROUND(n, 2) before casting to a tighter precision.

Gotcha 4 — Implicit string-to-string comparison is lexicographic.

The bug — '10' < '2' returns true because string comparison is character-by-character ('1' < '2').
Symptom — sorting numeric strings looks wrong; range predicates on string columns miss rows.
Fix — cast to numeric: '10'::int < '2'::int returns false. Better — store numeric data in numeric columns at the schema layer.

Gotcha 5 — CAST defeats indexes in WHERE clauses.

The bug — WHERE CAST(int_col AS VARCHAR) = '42' wraps the indexed column in a function call; the planner cannot use the B-tree index.
Symptom — query that should be milliseconds takes seconds; EXPLAIN shows a sequential scan.
Fix — cast the literal, not the column: WHERE int_col = CAST('42' AS INTEGER) — the planner can use the index on int_col.

Gotcha 6 — TIMESTAMP vs TIMESTAMPTZ casts shift values.

The bug — CAST(timestamp_col AS TIMESTAMPTZ) applies the session's time zone, silently shifting the wall-clock value.
Symptom — log timestamps off by N hours; reports in EU regions show US-time data.
Fix — use AT TIME ZONE to explicitly attach a zone — timestamp_col AT TIME ZONE 'UTC' — and confirm the session TZ.

Gotcha 7 — TRY_CAST masks bugs in trusted pipelines.

The trap — TRY_CAST returning NULL looks safe, but if the upstream pipeline guaranteed clean data, the NULL indicates a bug you should know about.
Symptom — downstream COALESCE(TRY_CAST(…), 0) silently swallows malformed rows; revenue totals undercount.
Fix — reserve TRY_CAST for untrusted boundaries (CSV ingest, user input); use plain CAST inside trusted pipelines so bugs surface loudly.

SQL
Topic — type-casting
Cast gotcha drills

Practice →

SQL
Topic — aggregation
Integer-division SQL drills

Practice →

Choosing the right conversion (cheat sheet)

A one-screen cheat sheet for using SQL CAST and CONVERT — pick the operator that matches your dialect and your safety needs.

You want to …	Operator	Notes
Convert to a different type, portably	`CAST(expr AS type)`	ANSI standard; every dialect
Convert to a different type in Postgres (concise)	`expr::type`	Postgres-only shorthand for `CAST`
Convert and format a date as string (SQL Server)	`CONVERT(VARCHAR, d, style)`	Use style codes (23, 103, 120, 126)
Convert without raising on bad input (SQL Server)	`TRY_CAST(expr AS type)`	Returns `NULL` on failure
Convert without raising on bad input (BigQuery)	`SAFE_CAST(expr AS type)`	Returns `NULL` on failure
Convert string to number (Snowflake safely)	`TRY_TO_NUMBER(s)`	Returns `NULL` on failure
Parse a non-ISO date string	`TO_DATE(s, pattern)` / `STR_TO_DATE` / `CONVERT(DATE, s, style)`	Dialect-specific
Fix integer division	`CAST(num AS NUMERIC) / denom`	Or `num::numeric / denom` in Postgres
Format numeric as currency	`TO_CHAR(n, '999,999.99')` / `FORMAT(n, 'C')`	Dialect-specific
Drop time from timestamp	`CAST(ts AS DATE)` or `ts::date`	Universal
Promote integer to decimal	`CAST(n AS NUMERIC(12,2))`	Safe widening
Truncate decimal to integer	`CAST(n AS INTEGER)`	Lossy; consider `ROUND` first
Round before casting	`CAST(ROUND(n, 2) AS NUMERIC(12,2))`	Explicit precision control
Cast literal in `WHERE` to keep index usable	`WHERE int_col = CAST('42' AS INT)`	Cast literal, not column

Frequently asked questions

What's the difference between CAST and CONVERT in SQL?

sql cast (signature CAST(expr AS type)) is the ANSI-standard explicit conversion operator — every major dialect supports it (PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery, SQLite). sql convert is the SQL Server / MySQL alternative, with two important differences. SQL Server's CONVERT(type, expr, style) accepts a third style argument controlling date-to-string and string-to-date formatting (style code 23 for ISO yyyy-mm-dd, 103 for UK dd/mm/yyyy, 120 for log timestamps) — that's the killer feature of sql convert. MySQL's CONVERT(expr, type) reverses the argument order (expr first, type second) and has no style argument. The portable interview answer to cast vs convert in sql: prefer CAST for any non-date conversion; reach for CONVERT in SQL Server when you need the style argument; never mix up SQL Server CONVERT(type, expr) with MySQL CONVERT(expr, type).

What is implicit type coercion in SQL?

Implicit type coercion is what the engine does silently when two different types meet in an expression — INTEGER + NUMERIC, VARCHAR = INTEGER, DATE < TIMESTAMP. The engine picks a "common supertype" and casts one or both sides. Numeric widening (INTEGER → NUMERIC → FLOAT) is generally safe. String / numeric comparison is dangerous — '10' < '2' returns true in lexicographic compare; WHERE int_col = '42' may defeat the index on int_col because the engine casts each row. Date / timestamp widening (DATE → TIMESTAMP) is safe but TIMESTAMP → TIMESTAMPTZ silently shifts the value by the session time zone. The senior answer: avoid implicit coercion entirely — write explicit CAST / CONVERT / ::type so reviewers and future readers can see what conversion fires.

When should I use TRY_CAST or SAFE_CAST instead of CAST?

Use the safe-cast operators (TRY_CAST in SQL Server / 2012+, SAFE_CAST in BigQuery, TRY_TO_NUMBER / TRY_TO_DATE in Snowflake) when the input is untrusted and a bad value should produce NULL instead of raising an error. The canonical use case is parsing CSV / JSON / user-input columns in a staging table — TRY_CAST(amount_str AS DECIMAL(12,2)) returns NULL for malformed rows, lets the clean rows load, and the dirty rows can be filtered into an error queue via WHERE TRY_CAST(…) IS NULL. Avoid TRY_CAST inside trusted pipelines — if the upstream guarantees clean data, a silent NULL masks a bug you'd want to know about. PostgreSQL has no built-in TRY_CAST pre-16; the workaround is a CASE WHEN col ~ '^pattern$' THEN col::type END regex guard, or a custom PL/pgSQL function that catches the cast exception.

How do I fix integer division in SQL?

The most-asked type casting in sql interview question. SELECT 5 / 2 returns 2 (integer truncated mean) on Postgres, SQL Server, Oracle, and Snowflake when both operands are integers — engineers expect 2.5. The fix is to cast at least one side to a fractional type before dividing: CAST(5 AS NUMERIC) / 2 returns 2.5; 5::numeric / 2 (Postgres shorthand) returns 2.5; 5 / 2.0 (implicit widening because of the decimal literal) returns 2.5. MySQL is the exception — it divides as floating point even with two integers (5 / 2 = 2.5), which is friendlier but inconsistent with the other dialects. The senior rule of thumb: always cast in division when you want a fractional result, regardless of dialect — the explicit code reads the same on every engine.

How do I parse a date string in different formats in SQL?

ISO YYYY-MM-DD is universal — CAST('2026-05-22' AS DATE) works on every dialect. For non-ISO strings, use the dialect's pattern-based parser. PostgreSQL: TO_DATE('22/05/2026', 'DD/MM/YYYY'). MySQL: STR_TO_DATE('22/05/2026', '%d/%m/%Y'). SQL Server: CONVERT(DATE, '22/05/2026', 103) (style 103 = UK dd/mm/yyyy) or TRY_CONVERT(DATE, …) for safe parsing. Oracle: TO_DATE('22/05/2026', 'DD/MM/YYYY'). Snowflake: TO_DATE('22/05/2026', 'DD/MM/YYYY') or the safer TRY_TO_DATE. The common bug is locale-dependence — without the explicit pattern, SQL Server uses the session's regional setting, which silently changes results across environments. Always pass the explicit pattern / style code when parsing non-ISO dates; never rely on implicit locale parsing.

Practice on PipeCode

PipeCode ships 450+ data-engineering interview problems — including PostgreSQL-first SQL practice keyed to type casting (CAST, CONVERT, ::type), safe parsing with TRY_CAST / SAFE_CAST, integer-division fixes, string ↔ date / numeric conversions, and the dialect quirks that fail candidates who memorise only one engine. Whether you're drilling sql interview questions with answers for sql for data engineers loops or grinding through data engineering interview questions end-to-end, the practice library mirrors the same explicit-vs-implicit mental model this guide teaches.

Kick off via Explore practice →; drill the dedicated type-casting SQL lane →; fan out into the date-functions SQL drills →; reinforce string-manipulation problems →; rehearse aggregation SQL drills →; widen coverage on the full SQL practice library →.

DEV Community

SQL CAST, CONVERT & Type Coercion: Safe Conversions for Data Engineers

1. Why CAST and CONVERT matter in data engineering interviews

Two families — explicit conversion and implicit coercion — that's the whole interview surface

Worked example — safe-parse a CSV column with explicit cast

2. Explicit vs implicit type coercion — the silent-result-changer

`implicit type conversion sql` — what the engine does when you don't ask

3. CAST(expr AS type) — the ANSI-standard cast

`cast in sql` — the conversion every dialect understands

SQL interview question — average order value per region, two-decimal display

Solution Using CAST + ROUND

4. CONVERT(type, expr, style) — SQL Server's superpower on dates

`convert in sql` — SQL Server's three-argument form with format styles

5. TRY_CAST, TRY_CONVERT, SAFE_CAST — error-swallowing safe casts

`try_cast in sql` — return NULL instead of raising on bad input

6. Numeric, string, and date conversion patterns

The three conversion paths every data engineer touches

7. Cast gotchas — lossy conversions, locale, comparison coercion

The seven bugs interviewers test most often on type conversion

Choosing the right conversion (cheat sheet)

Frequently asked questions

What's the difference between CAST and CONVERT in SQL?

What is implicit type coercion in SQL?

When should I use TRY_CAST or SAFE_CAST instead of CAST?

How do I fix integer division in SQL?

How do I parse a date string in different formats in SQL?

Practice on PipeCode

Top comments (0)

1. Why CAST and CONVERT matter in data engineering interviews

Two families — explicit conversion and implicit coercion — that's the whole interview surface

Worked example — safe-parse a CSV column with explicit cast

2. Explicit vs implicit type coercion — the silent-result-changer

implicit type conversion sql — what the engine does when you don't ask

3. CAST(expr AS type) — the ANSI-standard cast

cast in sql — the conversion every dialect understands

SQL interview question — average order value per region, two-decimal display

Solution Using CAST + ROUND

4. CONVERT(type, expr, style) — SQL Server's superpower on dates

convert in sql — SQL Server's three-argument form with format styles

5. TRY_CAST, TRY_CONVERT, SAFE_CAST — error-swallowing safe casts

try_cast in sql — return NULL instead of raising on bad input

6. Numeric, string, and date conversion patterns

The three conversion paths every data engineer touches

7. Cast gotchas — lossy conversions, locale, comparison coercion

The seven bugs interviewers test most often on type conversion

Choosing the right conversion (cheat sheet)

Frequently asked questions

What's the difference between CAST and CONVERT in SQL?

What is implicit type coercion in SQL?

When should I use TRY_CAST or SAFE_CAST instead of CAST?

How do I fix integer division in SQL?

How do I parse a date string in different formats in SQL?

Practice on PipeCode

`implicit type conversion sql` — what the engine does when you don't ask

`cast in sql` — the conversion every dialect understands

`convert in sql` — SQL Server's three-argument form with format styles

`try_cast in sql` — return NULL instead of raising on bad input