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 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;
  • 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.

learn more about sentryjoin our discord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.