Lesson 24 of 26 · see the practice database

Working with dates

Almost every real table has a date in it, and almost every report is "per month" or "in the last 30 days". Dates are also the part of SQL where the databases differ most. The ideas are the same everywhere. The function names are not. This lesson teaches the ideas using SQLite, which runs in the practice box, and ends with a table of the same jobs in other products.

Dates as text in year-month-day order

Our practice database stores dates as text such as '2026-03-15'. This form, year first, then month, then day, each with leading zeros, is an international standard (ISO 8601). It has a useful property: sorting the text alphabetically also sorts the dates in time order. That is why ORDER BY order_date and comparisons with < and > have worked all along.

SELECT id, order_date
FROM orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-09-01'
ORDER BY order_date;

Whatever database you use, write dates in queries in this form. '15/03/2026' means different things to different systems and people. '2026-03-15' means one thing.

Parts of a date

strftime formats a date using codes. It is SQLite's main date tool.

SELECT order_date,
       strftime('%Y', order_date) AS year,
       strftime('%m', order_date) AS month,
       strftime('%d', order_date) AS day
FROM orders
LIMIT 4;

The results are text. To use one as a number, convert it: CAST(strftime('%m', order_date) AS INTEGER).

Grouping by month

The most common date report. Format each date as year and month, then group by that.

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

Always include the year with the month. Grouping by the month number alone would add this January to last January.

Sales by month, joining in the prices:

SELECT strftime('%Y-%m', o.order_date) AS month,
       SUM(oi.quantity * p.price) AS sales
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE o.status <> 'cancelled'
GROUP BY strftime('%Y-%m', o.order_date)
ORDER BY month;

A month with no orders does not appear in such a report at all. That is the "groups with nothing in them" point from the lesson on GROUP BY. Reports that must show every month join to a table that lists the months.

Quarters

There is no quarter code in SQLite, so work it out from the month.

SELECT id, order_date,
       'Q' || ((CAST(strftime('%m', order_date) AS INTEGER) + 2) / 3) AS quarter
FROM orders
LIMIT 5;

The sum relies on whole-number division: months 1 to 3 give 1, months 4 to 6 give 2, and so on.

Day of the week

SELECT order_date,
       CASE strftime('%w', order_date)
         WHEN '0' THEN 'Sunday'    WHEN '1' THEN 'Monday'
         WHEN '2' THEN 'Tuesday'   WHEN '3' THEN 'Wednesday'
         WHEN '4' THEN 'Thursday'  WHEN '5' THEN 'Friday'
         ELSE 'Saturday'
       END AS weekday
FROM orders
LIMIT 5;

Adding and subtracting time

The date function takes a date and one or more changes to apply.

SELECT order_date,
       date(order_date, '+7 days') AS a_week_later,
       date(order_date, '+1 month') AS a_month_later,
       date(order_date, '-1 year') AS a_year_before
FROM orders
LIMIT 3;

Other useful changes move to the start of a period.

SELECT date('2026-03-15', 'start of month') AS first_of_month,
       date('2026-03-15', 'start of month', '+1 month', '-1 day') AS last_of_month,
       date('2026-03-15', 'start of year') AS first_of_year;

The middle column finds the last day of a month without knowing how many days the month has: go to the first of the month, forward one month, back one day.

Days between two dates

julianday turns a date into a count of days. Subtract two of them for the gap.

SELECT name, hired,
       CAST(julianday('2026-11-12') - julianday(hired) AS INTEGER) AS days_employed
FROM employees
ORDER BY days_employed DESC
LIMIT 4;

How long after joining did each customer first order?

SELECT c.name, c.joined, MIN(o.order_date) AS first_order,
       CAST(julianday(MIN(o.order_date)) - julianday(c.joined) AS INTEGER) AS days_to_first_order
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.joined
ORDER BY days_to_first_order
LIMIT 5;

Whole years between two dates

Dividing days by 365 is slightly wrong because of leap years. A dependable way to get completed years, such as an age or years of service, is to subtract the years and take one off if the anniversary has not yet come this year.

SELECT name, hired,
       CAST(strftime('%Y', '2026-11-12') AS INTEGER) - CAST(strftime('%Y', hired) AS INTEGER)
       - (strftime('%m-%d', '2026-11-12') < strftime('%m-%d', hired)) AS full_years
FROM employees
ORDER BY hired;

The comparison in brackets gives 1 when today's month and day come before the anniversary, and 0 otherwise.

Today's date

Every database can tell you the current date and time. In SQLite:

SELECT date('now') AS today, datetime('now') AS now_utc, datetime('now', 'localtime') AS now_local;

We do not show a result because it changes every day. Try it in the practice box. Note that SQLite's 'now' is in UTC, the world's reference time, unless you add 'localtime'. India is five and a half hours ahead of UTC, so late in the evening UTC the date differs.

The examples in this lesson use the fixed date '2026-11-12' so that their results stay the same. In real queries you would write date('now') in its place. "Orders in the last 30 days" becomes:

SELECT * FROM orders WHERE order_date >= date('now', '-30 days');

Dates with a time of day

A column that stores date and time together, such as '2026-03-15 14:30:00', needs care in comparisons. WHERE created = '2026-03-15' finds nothing, because no row was created at exactly midnight. Ask for a range:

WHERE created >= '2026-03-15' AND created < '2026-03-16'

This "from the start of the day, up to but not including the next day" pattern is correct in every database and lets an index be used.

The same jobs in other databases

Those databases have real date types, so the functions return dates and numbers, not text. EXTRACT(part FROM date) is the standard form and also works in MySQL and Oracle.

Advice

• Store dates in a date column, or as year-month-day text in SQLite. Never as day/month/year text.
• Compare dates with ranges: >= the start and < the day after the end.
• Group by year and month together.
• Avoid wrapping the date column in a function inside WHERE on large tables. WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' can use an index. WHERE strftime('%Y', order_date) = '2026' cannot.

Try it yourself

SELECT id, order_date FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';

SELECT strftime('%Y-%m', joined) AS month, COUNT(*) FROM customers GROUP BY strftime('%Y-%m', joined) ORDER BY month;

SELECT id, order_date, date(order_date, '+10 days') AS due_date FROM orders WHERE status = 'pending';

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.