In SQL how do you select dates between two dates?

Richard C.

October 15, 2023

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.

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;

Loved by over 4 million developers and more than 90,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.

Platform

Languages & Frameworks

Why Sentry?

Features

In SQL how do you select dates between two dates?

Richard C.

The problem

The solution

Select all rows between two absolute dates

Select all rows between months, years, or days

Select all dates between two dates

Related Answers

A better experience for your users. An easier life for your developers.

In SQL how do you select dates between two dates?

Richard C.

The solution

Select all rows between two absolute dates

Select all rows between months, years, or days

Select all dates between two dates

Related Answers

A better experience for your users. An easier life for your developers.

A peek at your privacy

Who we collect PII from

PII we may collect about you

How we use your PII

Third parties who receive your PII

We use cookies (but not for advertising)

Know your rights