Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 14 of 49

Dates and times in SQL: filter, extract and calculate

Filter by date range, extract years and months, add intervals and count days between dates in SQL, with checked output and notes for other databases.

16 minFunctions and expressionsUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonFilter rows by date range, extract the year or month, add to dates and count days between them, and know which parts are written differently on each database.

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:

sql
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '2023-01-01'
  AND hire_date <  '2024-01-01';
result
+------------+------------+
| 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:

sql
SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY hire_date;
result
+------------+------------+
| 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().

sql
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.

sql
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;
result
+------------+------------+-----------+--------------+
| 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):

sql
SELECT strftime('%Y', hire_date) AS year,
       COUNT(*)                  AS hires
FROM employees
GROUP BY strftime('%Y', hire_date)
ORDER BY year;
result
+------+-------+
| 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:

sql
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;
result
+------------+------------+-------------+----------------------+
| 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:

sql
SELECT first_name,
       hire_date,
       date(hire_date, '+1 year') AS first_anniversary
FROM employees
WHERE id IN (1, 19);
result
+------------+------------+-------------------+
| 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:

sql
SELECT date('2024-01-31', '+1 month') AS jan31_plus_a_month;
result
+--------------------+
| 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:

sql
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;
result
+-----------------+------------+------------+-----------+
|      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:

sql
SELECT date('not a date') AS broken;
result
+--------+
| 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) or EXTRACT. SQL Server: YEAR(d) or DATEPART(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_DATE in MySQL, PostgreSQL and SQLite. GETDATE() or CURRENT_TIMESTAMP in 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. Use YYYY-MM-DD.
  • Using BETWEEN with 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 on hire_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
sql
SELECT name,
       start_date,
       date(end_date, '+30 days') AS ends_after
FROM projects
WHERE end_date IS NOT NULL
ORDER BY ends_after;
result
+-----------------+------------+------------+
|      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 |
+-----------------+------------+------------+
sql
SELECT strftime('%m', hire_date) AS month,
       COUNT(*)                  AS hires
FROM employees
GROUP BY strftime('%m', hire_date)
ORDER BY month;
result
+-------+-------+
| 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.

Check yourself

1. Which date format is safe to compare and sort as text in every database?
2. Why is hire_date >= '2023-01-01' AND hire_date < '2024-01-01' a good way to select 2023?
3. Which statement about extracting the year from a date column is true?
4. On SQLite, what does date('not a date') return?