Numbers, dates and NULL

Arithmetic and round, SQLite's date functions, and handling NULL with coalesce.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗

main.sql

SELECT date('2026-01-31', '+1 month') AS next_month,
       strftime('%Y', '2026-10-20') AS year,
       julianday('2026-12-25') - julianday('2026-10-20') AS days_left,
       7 / 2 AS int_div,
       round(7 / 2.0, 1) AS real_div,
       coalesce(NULL, 'fallback') AS value,
       CASE WHEN 85 >= 80 THEN 'pass' ELSE 'retry' END AS result;

Result

next_month	year	days_left	int_div	real_div	value	result
2026-03-03	2026	66	3	3.5	fallback	pass

SQLite has no DATE type: dates are text ('2026-10-20'), and the date functions read and write that format.
date('2026-01-31', '+1 month') is March 3rd: February 31st rolls over. Check month arithmetic at the end of a month.
7 / 2 is 3 when both are integers; write 7 / 2.0 or CAST(x AS REAL) for a fraction.
Any arithmetic with NULL gives NULL; coalesce(a, b) returns the first value that is not NULL.
date('now') is the current UTC date, so a query using it gives a different result each day.

Numbers, dates and NULL

Related topics

Official references