SQL Type Conversion

SQL type conversion expresses a value using another data type. CAST(‘19.90’ AS DECIMAL(12,2)) produces a decimal amount from text. Sorting text uses textual comparison rules; converting quantities lets sorting and arithmetic use numeric meaning. Identifiers with leading zeros may need to remain text.

For a failed text-to-number conversion, CAST raises an error while DuckDB TRY_CAST returns NULL. Keep the original text and a failure flag. Otherwise ‘oops’, an empty field mapped to NULL, and a value missing at the source can become indistinguishable after conversion.

Success does not mean no information was lost. A decimal scale can round fractional digits, and converting a timestamp to DATE discards the time. A floating-point type is not a universal substitute for a decimal monetary amount. Choose precision, units, and time-zone interpretation before converting.

An expression in SELECT derives a result without altering the source column. Use separate validation for allowed ranges, codes, and business rules. For example, converting ‘-5’ to a number succeeds even when a negative order quantity is invalid. If a conversion failure later becomes zero through COALESCE, a pipeline can finish while reporting the wrong total.

Reference: DuckDB documentation. Work through the examples in SQL from Zero to Working.