# SQL: Select Rows Between Two Dates

> Query rows between two dates in SQL Server, PostgreSQL, and MySQL using BETWEEN, plus recursive CTEs to generate a full list of dates in a range

**URL:** https://sentry.io/answers/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.

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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;
```

---

*Source: [sentry.io/answers/in-sql-how-do-you-select-dates-between-two-dates/](https://sentry.io/answers/in-sql-how-do-you-select-dates-between-two-dates/)*