SQL Dates in SQLite: Filter a Month and Calculate Day Differences

Date queries are easier to trust when the storage format and time boundary are explicit. SQLite does not have a separate dedicated date storage type like some other databases. These examples use consistent ISO-formatted text dates and SQLite's date functions.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Build a tiny date example

The CTE below contains date-only text in year-month-day form. All values are valid dates with the same format.

WITH events(event_id, event_date) AS (
  VALUES (1, '2026-10-01'),
         (2, '2026-10-31'),
         (3, '2026-11-01')
)
SELECT event_id, event_date
FROM events
WHERE event_date >= '2026-10-01'
  AND event_date < '2026-11-01'
ORDER BY event_date, event_id;

Events 1 and 2 are included. Event 3 is not. A start-inclusive, end-exclusive range makes the intended month clear and avoids guessing the final instant of its last day.

Why consistent input matters

The direct text comparison works here because all entries use the same ordered ISO date-only representation. Mixing 1/10/2026, October 1 and timestamp strings with different timezone offsets breaks that simple assumption.

Inspect and normalise imported dates deliberately. Do not silently reinterpret an ambiguous value such as 03/04/2026, which may mean March 4 or April 3. Preserve the raw value until the intended date is confirmed.

Calculate elapsed days

SELECT julianday('2026-11-02') - julianday('2026-10-28')
       AS elapsed_days;

The answer is 5.0. julianday converts a recognised date or timestamp to a day-based number so subtraction can express a difference. A timestamp difference can include fractional days because hours and minutes matter too.

This is elapsed time, not an inclusive count of calendar dates. If your reporting rule includes both endpoints of a date-only interval, it would add one. Neither version automatically counts working days or removes holidays.

Extract a reporting month

SELECT strftime('%Y-%m', '2026-10-28') AS reporting_month;

The result is 2026-10. Including the year prevents October from several years being combined accidentally. A month number alone is insufficient for a multi-year report.

Use a fixed as-of date for a reproducible exercise. Functions based on the current time can make tomorrow's result differ from today's. In a real system, decide the timezone and whether you are comparing date-only deadlines or precise instants before writing the filter.

Check boundaries deliberately

Test the first included date, the last included date and the first excluded date. Add a missing value case if the field allows NULL. A query returning plausible rows in the middle of a month does not prove its boundaries are right.

Try it yourself

Using the same events CTE, return only November records with the same inclusive-start and exclusive-end pattern. What is the elapsed-day difference from 31 October to 1 November?

WITH events(event_id, event_date) AS (
  VALUES (1, '2026-10-01'),
         (2, '2026-10-31'),
         (3, '2026-11-01')
)
SELECT event_id, event_date
FROM events
WHERE event_date >= '2026-11-01'
  AND event_date < '2026-12-01'
ORDER BY event_date, event_id;

Keep exploring: SQL learning path.