CAST()
SQL · Date Functions
Theory
CAST converts a value from one data type to another: CAST('2026-11-08' AS DATE) turns text into a date, CAST('45000' AS INTEGER) turns text into a number, and CAST(order_ts AS DATE) drops the time part of a timestamp. The syntax CAST(value AS type) is standard SQL and works almost everywhere. PostgreSQL and Snowflake also accept the shorthand value::date.
Casting matters because the type decides behaviour. Text '900' sorts after text '1200' (because '9' > '1'), but as numbers 900 < 1200. A timestamp compared with a date may miss rows from later in the day unless you cast it first. If the text cannot be converted, most databases raise an error; SQL Server and Snowflake offer TRY_CAST, which returns NULL instead. SQLite is dynamically typed, so CAST(x AS INTEGER) works, but to turn a timestamp into a date you use date(x).
Why we use it
Real data often arrives with the wrong type: numbers stored as text, timestamps where you need dates. CAST makes the type explicit so sorting, maths, comparisons and joins behave the way you expect.
When to use it
Use CAST when cleaning staged data, comparing a timestamp to a date, or doing maths on numeric text. For non-ISO date text use TO_DATE with a mask; for formatting output use TO_CHAR. Use TRY_CAST on messy input.
How it works
- Value of the 'wrong' type arrives
- Write CAST(value AS target_type)
- Can the value be converted? Yes: Return the new value. No: Error (NULL with TRY_CAST).
- Sorting, maths and comparisons now follow the new type
- Clean, correctly typed column
Syntax
CAST(value AS type)Example
SELECT CAST(hire_date AS DATE) FROM employees;Key points
- Standard SQL, works the same across almost every dialect
- SQLite is dynamically typed, so CAST mostly affects comparisons/formatting
- PostgreSQL/Snowflake shorthand: value::date, value::int
- TRY_CAST (SQL Server, Snowflake) returns NULL instead of raising an error
- Text numbers sort alphabetically ('900' > '1200') until you cast them