sqlstreak

SQL date functions

Dates power most reports: this month's signups, days since the last order, revenue by week. The ideas are the same everywhere, but PostgreSQL and SQLite spell them differently. Switch the dialect on any example to compare.

Today's date

CURRENT_DATE works in both. (SQLite also has date('now').) Dates are in UTC unless you convert them.

SELECT CURRENT_DATE AS today;

Filtering by date

Compare against ISO dates like '2026-01-01'. This works the same in both databases.

SELECT name, arrived_on
FROM pets
WHERE arrived_on >= '2026-01-01' AND arrived_on < '2026-04-01'
ORDER BY arrived_on;

Extracting parts

PostgreSQL uses EXTRACT(part FROM date). SQLite formats with strftime, which returns text.

SELECT name,
  strftime('%Y', arrived_on) AS year,
  strftime('%m', arrived_on) AS month,
  strftime('%w', arrived_on) AS weekday
FROM pets;

Grouping by month

Turn each date into its month, then group. PostgreSQL can also use date_trunc('month', d).

SELECT strftime('%Y-%m', arrived_on) AS month, COUNT(*) AS arrivals
FROM pets
GROUP BY month
ORDER BY month;

Adding days and measuring gaps

PostgreSQL adds integers to dates and subtracts dates to get days (or use INTERVAL '1 month'). SQLite uses date(d, '+7 days') and julianday().

SELECT name, arrived_on,
  date(arrived_on, '+30 days') AS review_on,
  CAST(julianday('2026-11-17') - julianday(arrived_on) AS INTEGER) AS days_here
FROM pets;

Common mistakes

  • Storing dates as text in a non-ISO format (03/01/2026), which sorts and compares incorrectly.

  • BETWEEN '2026-03-01' AND '2026-03-31' on timestamps, which misses everything after midnight on the 31st. Use >= start AND < next_day.

  • Forgetting that SQLite's strftime returns text, so '03' isn't the number 3 unless you CAST it.

Questions

Does SQLite have a DATE type?+

No. Dates are stored as ISO text (or numbers), and the date functions understand that format.

How do I get the start of the week?+

PostgreSQL: date_trunc('week', d) (Monday). SQLite: date(d, 'weekday 0', '-6 days') gives the Monday of d's week.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now