Working with Dates

Dates are everywhere in real data. SQLite stores dates as text in 'YYYY-MM-DD' format.

Get today's date:

SELECT date('now');

Extract parts of a date with strftime(): strftime('%Y', hire_date) -- year: '2024' strftime('%m', hire_date) -- month: '03' strftime('%d', hire_date) -- day: '15'

Calculate days between dates:

SELECT julianday('now') - julianday(hire_date) AS days_employed
FROM employees;

Filter by date ranges:

SELECT * FROM employees
WHERE hire_date >= '2024-01-01'
AND hire_date < '2025-01-01';

Date math with date(): date('now', '-30 days') -- 30 days ago date('now', '+1 year') -- 1 year from now