Working with dates

Why dates need special care

Almost every business question has a date in it: orders this month, customers who joined last year, invoices overdue by 30 days. Dates look simple, but months have different lengths, years can be leap years, and people write dates in many forms (15/03/2025, 03-15-2025, 15 Mar 2025). The database needs one clear form to work with.

ISO dates in SQLite

SQLite stores dates as text in the ISO 8601 form: YYYY-MM-DD, for example '2025-03-15', and date-times as 'YYYY-MM-DD HH:MM:SS'. ISO means the international standard. Because the biggest part (the year) comes first and every part has a fixed width, ISO dates sort and compare correctly as plain text, which is why WHERE order_date >= '2025-03-01' worked in module 2.

The SQLite date functions

strftime means "string from time": its first argument is a format with codes like %Y (year), %m (month), %d (day), %H (hour) and %M (minute), and it returns text. julianday turns a date into a day number counted from a fixed start, so subtracting two of them gives the days between. The result is a real number; wrap it in CAST(... AS INTEGER) for whole days.

SELECT id, order_date, status
FROM orders
WHERE strftime('%Y-%m', order_date) = '2025-03';
-- 105 | 2025-03-02 | Shipped
-- 106 | 2025-03-15 | Delivered
-- 107 | 2025-03-28 | Placed

SELECT id, order_date, date(order_date, '+7 days') AS deliver_by
FROM orders WHERE status IN ('Placed', 'Shipped');
-- 105 | 2025-03-02 | 2025-03-09
-- 107 | 2025-03-28 | 2025-04-04
-- 108 | 2025-04-02 | 2025-04-09

Today's date

date('now') gives today's date and datetime('now') the current date and time, in UTC (world time, 5 hours 30 minutes behind India). Add 'localtime' for the computer's own time zone: datetime('now', 'localtime'). Because the answer changes every day, practice tasks in this course use a fixed date such as '2025-12-31' instead.

The same jobs in other databases

-- PostgreSQL only
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;
-- MySQL only
SELECT DATEDIFF('2025-12-31', joined) AS days_member FROM customers;

Common mistakes

• Comparing strftime('%m', d) with the number 3. strftime returns the text '03', and in SQLite '03' = 3 is false. Compare with '03', or CAST it to INTEGER.
• Adding a month to 31 January. date('2025-01-31', '+1 month') gives 2025-03-03 in SQLite, because 31 February does not exist. Check month-end logic, or use 'start of month' first.
• Forgetting that julianday differences are real numbers. Use CAST(... AS INTEGER) or ROUND for whole days.
• Storing dates as '15/03/2025'. They sort wrongly and the date functions return NULL. Store ISO dates and format them only for display.
• Using date('now') in a report without thinking about time zones: just after midnight in India, UTC is still on the previous day.