In SQL how do you select dates between two dates?

The problem

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.

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:

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:

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:

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:

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:

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:

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:

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:

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;