Comparing Dates

SQLite has no special type for dates. Pixel Shop keeps them as text in the form 'YYYY-MM-DD': the year, the month and the day, with a zero in front of one-digit months and days. March 14, 2024 is '2024-03-14'. This form is an international standard (ISO 8601), and nearly every database and programming language understands it.

In a condition, you write a date like any other text, in single quotes. The orders from Valentine's Day 2025:

SELECT id, customer_id, ordered_at
FROM orders
WHERE ordered_at = '2025-02-14';

The other comparisons work with dates too, and they give the right answers thanks to the order of the parts. Text is compared from the first character, so the year decides first, then the month, and then the day, just as with dates. You saw that in the previous lesson, when ORDER BY sorted such dates from the oldest. An earlier date is less, a later one is greater. The orders from December 2025 on:

SELECT id, ordered_at
FROM orders
WHERE ordered_at >= '2025-12-01'
ORDER BY ordered_at;

For a whole year, ask for the dates from January 1 of that year (included) to January 1 of the next year (not included). The orders from 2024:

SELECT id, ordered_at
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at < '2025-01-01'
ORDER BY ordered_at;

Why < '2025-01-01' and not <= '2024-12-31'? With dates like the shop's, both work. But some columns hold a time too, such as '2024-12-31 18:30', and that text is greater than '2024-12-31', so <= would leave out the evening of New Year's Eve. “Less than the first day of the next year” is right every time.

Rows with NULL instead of a date, such as Robot Rebellion with no release date, aren't found by any of these conditions. You'll learn why in the last exercise of this lesson.

Typical mistake: a date without quotes. WHERE released > 2024-01-01 isn't an error, but SQLite reads 2024-01-01 as a calculation: 2024 minus 1 minus 1, which is 2022. The query then finds the games released from 2022 on. A date written in another form, such as '3/14/2024' or '2024-3-14', is no better: it's compared as text, character by character, and the results are wrong without any error.