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.

Answer 1

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

Answer 2

Does this help you ?

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

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

Answer 3

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.

Answer 4

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