Select Date Between Two Columns

Question

I need a query in SQL.
If I have two columns STARTDATE and END_DATE.
I want to select a all rows where a date falls between these two dates.

e.g.: startdate = 1/1/2011 AND enddate = 2/2/2011.

Do you mean you need one row for each of the individual dates between start and end in the result set? — Yuck, Aug 25 '11 at 12:47
possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — Jacob, Aug 25 '11 at 12:47
If this is MySQL. If not, sorry for the quick dupe close vote :) — Jacob, Aug 25 '11 at 12:47
Too all the close voters, how is this not a real question? It is clear as glass that the OP wants to do `SELECT * FROM t1 WHERE ? BETWEEN startdate AND enddate` — Johan, Aug 25 '11 at 13:32

Johan · Accepted Answer · 2011-08-25T13:29:48.117

29

SELECT * FROM table1 
WHERE '2011-01-01' BETWEEN table1.startdate AND table1.enddate

Replace the explicit date either with now() or a parameter or whatever.

If the enddate is not defined as NOT NULL you can do:

SELECT * FROM table1 
WHERE '2011-01-01' BETWEEN table1.startdate AND COALESCE(table1.enddate, NOW())

edited Aug 25 '11 at 13:29

answered Aug 25 '11 at 13:09

Johan

2

+1 - But I would change enddate to `COALESCE(enddate,now())` – Lamak Aug 25 '11 at 13:26
If the `enddate` is defined as NOT NULL, good suggestion. – Johan Aug 25 '11 at 13:28

score 5 · Answer 2 · answered Aug 25 '11 at 13:09

5

Does this help you ?

select * 
from table 
where START_DATE < NOW() AND END_DATE > NOW()

Depending on the database, use CURRENT_TIMESTAMP() or TODAY()

answered Aug 25 '11 at 13:09

Jean-Charles

score 1 · Answer 3 · answered Aug 25 '11 at 13:11

1

Do you mean this:

select *
from mytable
where start_date >= '01/01/2011'
and end_date <= '02/01/2011'

Until you do more to clarify your question, it's hard for us to provide better answers.

answered Aug 25 '11 at 13:11

John N

score 0 · Answer 4 · answered Jan 06 '23 at 09:02

0

SELECT Count (DISTINCT id) FROM Table WHERE rqmnt_start_date::TIMESTAMP >= '27/01/2022' and rqmnt_end_date::TIMESTAMP <= '30/11/2022'

answered Jan 06 '23 at 09:02

NARENDRA AHIRWAR

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 06 '23 at 17:49

4 Answers4