Converts a value from one type to another: text to a number, text to a date, a number to text.
CAST(<value> AS <type>)
<value>::<type>
The ::<type> form does the same and is shorter, but it exists only in PostgreSQL. You need it when data arrives as text or when a type gets in the way of a calculation.
orders
| id | created_at | amount |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 120.50 |
| 2 | 2026-07-28 18:40:00 | 80.25 |
| 3 | 2026-08-09 09:05:00 | 199.99 |
| 4 | 2026-08-21 14:30:00 | 45.00 |
| 5 | 2026-09-02 11:00:00 | 64.75 |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| number | day |
|---|---|
| 43 | 2026-09-13 |
| number | week_later |
|---|---|
| 43 | 2026-09-20 |
You can convert to a date and add days to it right away.
| id | amount | whole |
|---|---|---|
| 1 | 120.50 | 121 |
| 2 | 80.25 | 80 |
| 3 | 199.99 | 200 |
| 4 | 45.00 | 45 |
| 5 | 64.75 | 65 |
120.50 became 121: converting to integer rounds rather than drops the fractional part.
If the text doesn't look like the target type, the query fails: CAST('abc' AS integer) — invalid input syntax for type integer. One “broken” row in a table breaks the whole query.
Converting a fractional number to integer rounds it: CAST(2.5 AS integer) gives 3. To simply drop the fractional part, use TRUNC.
:: is PostgreSQL only. If the query has to work in other DBMSs as well, write CAST(… AS …).