Strings and dates Updated November 2026

CAST

CAST(expression AS type) converts a value to another type: CAST('42' AS INTEGER) gives 42, CAST(price AS TEXT) gives a string, CAST(7 AS REAL) / 2 gives 3.5 instead of 3. PostgreSQL also accepts the shorthand expression::type. SQLite only has INTEGER, REAL, TEXT, BLOB and NUMERIC; PostgreSQL has DATE, NUMERIC(10,2), BOOLEAN and more.

Syntax

CAST(expression AS type)   PostgreSQL also: expression::type

Part	What it is
`expression`	The value or column to convert
`type`	Target type: INTEGER, REAL, TEXT, NUMERIC, DATE, and so on

Examples

SELECT CAST('42' AS INTEGER) + 1 AS n;

Result: n 43

SELECT 7 / 2 AS int_div, CAST(7 AS REAL) / 2 AS real_div;

Result: int_div | real_div 3 | 3.5

Integer divided by integer truncates in SQLite and PostgreSQL. Multiplying by 1.0 (7 * 1.0 / 2) is a common shortcut.

SELECT 'Order ' || CAST(id AS TEXT) AS label FROM orders;

Result: label Order 1

Both databases would also accept 'Order ' || id, but the explicit cast is clearer and portable.

SELECT CAST('2025-03-15' AS DATE) + 30;

Result: 2025-04-14

PostgreSQL only; SQLite has no DATE type, use date('2025-03-15', '+30 days').

Common mistakes

CAST('abc' AS INTEGER) returns 0 in SQLite but raises invalid input syntax in PostgreSQL; clean the data or filter rows first.
Expecting CAST(3.9 AS INTEGER) to round; it truncates to 3 in both databases, so use ROUND first.
Casting a DATE to TEXT in PostgreSQL loses the ability to add intervals; keep dates as dates and format only for display with to_char.

Practice CAST

These drills check your answer on live data, free, no account needed.

Calculated columnReturn each product's name and its price after a 10% discount.
Share of revenueFor each country, return the country and its share of total paid revenue in percent, rounded to 1 decimal.

Questions

What is the difference between CAST and CONVERT?

CAST is standard SQL and works in SQLite, PostgreSQL, MySQL and SQL Server. CONVERT is SQL Server and MySQL syntax with a different argument order.

How do I convert a string to a number in SQL?

CAST(s AS INTEGER) or CAST(s AS REAL). In PostgreSQL s::numeric also works and errors on bad input; SQLite returns 0 for text it cannot parse.

String functionsThe string functions you use in SQLite and PostgreSQL: || for concatenation, UPPER and LOWER, LENGTH, SUBSTR, TRIM, REPLACE, INSTR and POSITION, with notes.
Date functionsGet today's date, extract the year or month, group by month, add days and compute date differences in SQLite (strftime, julianday) and PostgreSQL (EXTRACT).
SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.
COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.