17

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.

Johan
  • 73,011
  • 23
  • 185
  • 311
ConquistadorAravinth
  • 272
  • 1
  • 2
  • 15
  • 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
  • 5
    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

3 Answers3

28
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())

See: http://www.1keydata.com/sql/sql-coalesce.html

Johan
  • 73,011
  • 23
  • 185
  • 311
4

Does this help you ?

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

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

Jean-Charles
  • 1,650
  • 17
  • 26
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.

John N
  • 1,705
  • 16
  • 21