After this lesson you can answer date questions with SQL: who joined in 2023, how many days a project ran, when each person's first anniversary falls, and how many hires there were each year. You will also know why date code is the one area where you should expect to rewrite a query when you change database.
The examples are tested on SQLite using the sample company database. Because date features differ so much, the lesson shows the SQLite way for each result and then lists the other databases' versions side by side.
How dates are stored
Most databases have real DATE, TIME and TIMESTAMP types. They accept text in the standard 'YYYY-MM-DD' format, such as '2024-03-12', and store it in a compact form. SQLite is the exception: it has no date type at all, and keeps dates as ordinary text in that same format. The sample database's hire_date column is declared as DATE, but in SQLite it holds text like 2018-03-12.
The year-month-day format with leading zeros is not just a habit. Because the biggest unit comes first, alphabetical order is also chronological order, so sorting and comparing work without any special functions.
Filtering by date
Comparison operators work on dates the way they work on numbers:
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '2023-01-01'
AND hire_date < '2024-01-01';+------------+------------+
| first_name | hire_date |
+------------+------------+
| Tara | 2023-04-03 |
| Sana | 2023-07-17 |
+------------+------------+This picks out everyone hired during 2023. The pattern, "on or after the first day, and strictly before the first day of the next period", is the safest way to select a period. The reason is that many date columns also hold a time of day. A test such as BETWEEN '2023-01-01' AND '2023-12-31' would stop at midnight at the start of 31 December and miss everything later that day. The half-open range has no such gap, and it works for months and quarters too.
BETWEEN is fine for pure dates with no time part, as in this column:
SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY hire_date;+------------+------------+
| first_name | hire_date |
+------------+------------+
| Ravi | 2024-01-08 |
| Aman | 2024-03-18 |
| Diya | 2024-06-10 |
+------------+------------+Today's date
Standard SQL provides CURRENT_DATE for the current date and CURRENT_TIMESTAMP for the date and time. They work in MySQL, PostgreSQL and SQLite. SQL Server supports CURRENT_TIMESTAMP and also GETDATE().
SELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS right_now;No output is shown for this one, because it changes every time you run it and any value printed here would soon be wrong. Run it yourself to see.
Pulling out the year, month or day
Reports often group or filter by a part of a date. In standard SQL you write EXTRACT(YEAR FROM hire_date). PostgreSQL and MySQL accept that. SQLite has no EXTRACT; its tool is strftime(format, date), which formats a date using codes such as %Y for the four-digit year, %m for the month and %d for the day. It returns text, so cast it if you need a number.
SELECT first_name,
hire_date,
strftime('%Y', hire_date) AS year_text,
CAST(strftime('%m', hire_date) AS INTEGER) AS month_number
FROM employees
WHERE id <= 4;+------------+------------+-----------+--------------+
| first_name | hire_date | year_text | month_number |
+------------+------------+-----------+--------------+
| Meera | 2018-03-12 | 2018 | 3 |
| Arjun | 2019-07-01 | 2019 | 7 |
| Kavya | 2020-01-20 | 2020 | 1 |
| Rohan | 2021-06-14 | 2021 | 6 |
+------------+------------+-----------+--------------+Once you can extract the year, you can group by it. The next query counts hires per year (the lesson on GROUP BY explains the grouping part in detail):
SELECT strftime('%Y', hire_date) AS year,
COUNT(*) AS hires
FROM employees
GROUP BY strftime('%Y', hire_date)
ORDER BY year;+------+-------+
| year | hires |
+------+-------+
| 2016 | 1 |
| 2017 | 1 |
| 2018 | 2 |
| 2019 | 3 |
| 2020 | 3 |
| 2021 | 2 |
| 2022 | 3 |
| 2023 | 2 |
| 2024 | 3 |
+------+-------+Adding and subtracting time
Here the databases differ the most. SQLite gives date() a start date followed by modifiers:
SELECT date('2024-03-12', '+30 days') AS in_30_days,
date('2024-03-12', '-1 year') AS year_ago,
date('2024-03-12', 'start of month') AS month_start,
date('2024-03-12', '+1 month', '-1 day') AS one_month_less_a_day;+------------+------------+-------------+----------------------+
| in_30_days | year_ago | month_start | one_month_less_a_day |
+------------+------------+-------------+----------------------+
| 2024-04-11 | 2023-03-12 | 2024-03-01 | 2024-04-11 |
+------------+------------+-------------+----------------------+A typical use is an anniversary date. This lists the first anniversary of two employees:
SELECT first_name,
hire_date,
date(hire_date, '+1 year') AS first_anniversary
FROM employees
WHERE id IN (1, 19);+------------+------------+-------------------+
| first_name | hire_date | first_anniversary |
+------------+------------+-------------------+
| Meera | 2018-03-12 | 2019-03-12 |
| Diya | 2024-06-10 | 2025-06-10 |
+------------+------------+-------------------+Month arithmetic needs care at the end of a month. There is no 31 February, so what should '2024-01-31' plus one month be? SQLite counts the extra days and rolls forward:
SELECT date('2024-01-31', '+1 month') AS jan31_plus_a_month;+--------------------+
| jan31_plus_a_month |
+--------------------+
| 2024-03-02 |
+--------------------+Other databases make a different choice, so if end-of-month results matter, test your database with a date like this one.
Counting days between dates
In SQLite, julianday() converts a date to a day number, so subtracting two of them gives the number of days between them:
SELECT name,
start_date,
end_date,
CAST(julianday(end_date) - julianday(start_date) AS INTEGER) AS days_long
FROM projects
WHERE end_date IS NOT NULL;+-----------------+------------+------------+-----------+
| name | start_date | end_date | days_long |
+-----------------+------------+------------+-----------+
| Customer Portal | 2024-01-15 | 2024-09-30 | 259 |
| Spring Campaign | 2025-02-01 | 2025-04-30 | 88 |
| Q3 Sales Push | 2024-07-01 | 2024-09-30 | 91 |
+-----------------+------------+------------+-----------+Projects that are still running have a NULL end date, and any calculation with NULL gives NULL, so the query filters them out. The lesson on NULL values explains why.
Bad dates
Dates that SQLite cannot read do not raise an error. They quietly turn into NULL:
SELECT date('not a date') AS broken;+--------+
| broken |
+--------+
| NULL |
+--------+This is easy to miss in a large result, so validate the data you load. Other databases are stricter and usually refuse to store an impossible date such as 31 February in a real date column.
Differences between databases
This list is the most useful part of the lesson to keep nearby when you change systems. Each item shows how to do the same job.
- Year of a date. Standard and PostgreSQL:
EXTRACT(YEAR FROM d). MySQL:YEAR(d)orEXTRACT. SQL Server:YEAR(d)orDATEPART(year, d). SQLite:strftime('%Y', d). - Add 30 days. MySQL:
DATE_ADD(d, INTERVAL 30 DAY). PostgreSQL:d + INTERVAL '30 days'. SQL Server:DATEADD(day, 30, d). SQLite:date(d, '+30 days'). - Days between two dates. MySQL:
DATEDIFF(later, earlier). SQL Server:DATEDIFF(day, earlier, later), with the order of arguments reversed. PostgreSQL: subtracting two dates,later - earlier, gives a number of days. SQLite:julianday(later) - julianday(earlier). - Current date.
CURRENT_DATEin MySQL, PostgreSQL and SQLite.GETDATE()orCURRENT_TIMESTAMPin SQL Server.
For MySQL's full set of functions see date and time functions in MySQL.
Common mistakes
- Using a local format such as
12/03/2018. It is ambiguous (12 March or 3 December?) and does not sort. UseYYYY-MM-DD. - Using
BETWEENwith a timestamp. The last day of the range loses all rows after midnight. Use a half-open range. - Wrapping the column in a function inside
WHERE.WHERE strftime('%Y', hire_date) = '2023'works, but it usually stops the database from using an index onhire_date. A range test is faster on big tables. - Copying a date function between databases.
DATEDIFF, for example, means different things in MySQL and in SQL Server. - Trusting dates stored as text. SQLite will happily store
'tomorrow'in a date column.
Exercise
List each project with its name, start date and a column ends_after showing the end date 30 days after the project's own end_date. Only include projects that have an end date, and sort by the new column. Then write a second query that counts how many people were hired in each month number across all years (so all Marches together), sorted by month.
Show one possible answer
SELECT name,
start_date,
date(end_date, '+30 days') AS ends_after
FROM projects
WHERE end_date IS NOT NULL
ORDER BY ends_after;+-----------------+------------+------------+
| name | start_date | ends_after |
+-----------------+------------+------------+
| Customer Portal | 2024-01-15 | 2024-10-30 |
| Q3 Sales Push | 2024-07-01 | 2024-10-30 |
| Spring Campaign | 2025-02-01 | 2025-05-30 |
+-----------------+------------+------------+SELECT strftime('%m', hire_date) AS month,
COUNT(*) AS hires
FROM employees
GROUP BY strftime('%m', hire_date)
ORDER BY month;+-------+-------+
| month | hires |
+-------+-------+
| 01 | 3 |
| 02 | 2 |
| 03 | 2 |
| 04 | 2 |
| 05 | 1 |
| 06 | 2 |
| 07 | 2 |
| 08 | 1 |
| 09 | 2 |
| 10 | 1 |
| 11 | 2 |
+-------+-------+These use SQLite's date functions. On other databases, swap in the equivalents from the list above.
Key points
- Write dates as 'YYYY-MM-DD'. This ISO format sorts and compares correctly, and every database understands it.
- For date ranges, prefer a start-inclusive, end-exclusive test such as hire_date >= '2023-01-01' AND hire_date < '2024-01-01'. It also works when a column holds a time.
- Date functions are the least portable part of SQL. EXTRACT, YEAR(), strftime and DATEPART all pull out a year, depending on the database.
- SQLite has no real date type. It stores dates as text, so only the formats it understands will work with its date functions.