Sentry Answers>SQL>

In SQL how do you select dates between two dates?

In SQL how do you select dates between two dates?

Richard C.

The problemJump To Solution

If you have a table in SQL (either SQL Server, PostgreSQL, or MySQL) that has a column with dates, how do you select all rows that contain a date between a start and end date?

Alternatively, given two dates, how do you list all dates between them?

The solution

Assume you have a table called Person with some birth dates and death dates. The following creation query will work on most database servers.

Click to Copy
CREATE TABLE Person ( Id INT PRIMARY KEY, Birthdate DATE, Deathdate DATE ); INSERT INTO Person (Id, Birthdate, Deathdate) VALUES (1, '1901-01-01', '2001-01-01'), (2, '1902-02-02', '2002-02-01'), (3, '1903-03-03', '2003-03-01'), (4, '1904-04-04', '2004-04-01'), (5, '1905-05-05', '2005-05-01'), (6, '1906-06-06', '2006-06-01'), (7, '1907-07-07', '2007-07-01'), (8, '1908-08-08', '2008-08-01');

Select all rows between two absolute dates

To select all rows with births between the 1902 and 1907 (inclusive) you can run:

Click to Copy
SELECT * FROM Person WHERE Birthdate BETWEEN '1902-01-01' AND '1907-01-01'; -- Id Birthdate Deathdate -- 2 1902-02-02 2002-02-01 -- 3 1903-03-03 2003-03-01 -- 4 1904-04-04 2004-04-01 -- 5 1905-05-05 2005-05-01 -- 6 1906-06-06 2006-06-01

If you want to query multiple date columns you can use < and > operators:

Click to Copy
SELECT * FROM Person WHERE Birthdate > '1902-01-01' AND Deathdate < '2007-01-01'; -- Id Birthdate Deathdate -- 2 1902-02-02 2002-02-01 -- 3 1903-03-03 2003-03-01 -- 4 1904-04-04 2004-04-01 -- 5 1905-05-05 2005-05-01 -- 6 1906-06-06 2006-06-01

Select all rows between months, years, or days

To select all rows between the February and March, regardless of year, in SQL Server and MySQL you can run:

Click to Copy
SELECT * FROM Person WHERE Month(Birthdate) BETWEEN 2 AND 3; -- Id Birthdate Deathdate -- 2 1902-02-02 2002-02-01 -- 3 1903-03-03 2003-03-01

Or in Postgres:

Click to Copy
SELECT * FROM Person WHERE EXTRACT(Month FROM Birthdate) BETWEEN 2 AND 3;

You can replace Month in the above commands with Year or Day to search between years or days.

Select all dates between two dates

Now suppose you want to list all dates between two given dates, without any specific table. In MySQL this query will work:

Click to Copy
WITH RECURSIVE d AS ( SELECT '1901-01-01' AS DATE UNION ALL SELECT DATE + INTERVAL 1 DAY FROM d WHERE DATE < '1901-01-08' ) SELECT * FROM d; -- date -- 1901-01-01 -- 1901-01-02 -- 1901-01-03 -- 1901-01-04 -- 1901-01-05 -- 1901-01-06 -- 1901-01-07 -- 1901-01-08

You need to edit the command slightly for PostgreSQL:

Click to Copy
WITH RECURSIVE d AS ( SELECT DATE '1901-01-01' AS DATE UNION ALL SELECT (DATE + INTERVAL '1 DAY')::DATE FROM d WHERE DATE < '1901-01-08' ) SELECT * FROM d;

This technique is called a recursive common table expression (CTE) and works with all modern database servers. However, there is a neater PostgreSQL specific way of listing dates:

Click to Copy
SELECT generate_series ( '1901-01-01'::DATE, '1901-01-07'::DATE, interval '1 day' )::DATE AS date;

In Microsoft SQL Server the CTE command works too, but you need to edit the syntax again:

Click to Copy
WITH d AS ( SELECT CAST('1901-01-01' AS DATE) AS DATE UNION ALL SELECT DATEADD(DAY, 1, DATE) FROM d WHERE DATE < '1901-01-08' ) SELECT * FROM d;
  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.