~/learn/SQL · Dates & text
Working with dates
Extract parts, do date arithmetic and roll data up by month.
why this matters
Most business questions are about time: sales this month, sign-ups per week, growth year-on-year.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
Dates are stored here as ISO text ('2023-06-01'). That format sorts chronologically as plain text, and strftime pulls out any part of it.
Try it: Show the day of the year (%j) instead of the weekday.
date() applies modifiers to shift a date, and julianday() turns two dates into a difference in days — the usual way to measure age, lag or time-to-event.
Reporting almost always means grouping by a truncated date. Truncate to the month with strftime, then aggregate.
Try it: Count only completed orders in the monthly totals.
Note
Other engines spell this differently — DATE_TRUNC('month', col) and EXTRACT(YEAR FROM col) in Postgres, DATE_FORMAT in MySQL — but the idea is identical.
Common gotcha
SQLite stores dates as text like '2024-03-15'. Always keep the YYYY-MM-DD format so sorting and comparisons work.
key takeaways
- strftime('%Y-%m', date) extracts the month.
- date(d, '+7 days') shifts a date.
- Compare dates as YYYY-MM-DD text.
check your understanding
How do you group orders by month in SQLite?
free practice
Build confidence with a question you can solve without Pro.