Working with Dates

Dates in Pixel Shop are texts in the form 'YYYY-MM-DD'. SQLite has no special type for dates, but it has functions that understand such texts and can calculate with them.

strftime(format, date) (from “string format time”) writes a date in another form. The format is a text in which %Y stands for the year, %m for the month and %d for the day; everything else in it is copied as it is:

SELECT title, released,
       strftime('%Y', released) AS year,
       strftime('%m/%d/%Y', released) AS us_date
FROM games
LIMIT 3;

The result of strftime is always a text, even when it looks like a number: the year is '2024', not 2024. You'll see why that matters later in this lesson. Watch out for capitals in the format: %M isn't the month but the minutes, which are 00 in a date without a time.

date(date, modifier) moves a date. The modifier is a text such as '+7 days', '-1 month' or '+1 year', and the result is a date in the usual form again:

SELECT date('2024-03-14', '+7 days') AS week_later,
       date('2024-03-14', '-1 month') AS month_before,
       date('2024-03-14', 'start of month') AS first_day;

date knows how many days each month has, leap years included: a week after 2024-02-25 is 2024-03-03. You can give it several modifiers, and it applies them in order: date(released, 'start of month', '+1 month', '-1 day') is the last day of the month in which a game came out. One thing to watch: a month after January 31 would be February 31, which doesn't exist, so SQLite goes on into March: date('2024-01-31', '+1 month') is 2024-03-02.

The date functions are usually written in lowercase, as SQLite's documentation writes them; like all names of functions, they work in capitals too. A special date is 'now': date('now') is today's date, and date('now', '+30 days') the date in 30 days. Real apps use it all the time, but these exercises don't, because its result changes every day.

Typical mistake: a format or a modifier without quotes. strftime(%Y, released) fails with near "%": syntax error. Formats and modifiers are texts, so they go in single quotes.