Select records between two dates in two columns

Question

Hi,

How do I Select records between two dates in two columns?

Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo

I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDateTo.

Sample

Record 1 ColumnDateFrom = 2009-08-01 AND ColumnDateTo = 2009-10-01

Record 2 ColumnDateFrom = 2010-08-01 AND ColumnDateTo = 2010-10-01

If my input date is 2009-09-28; then I get record 1

Answer 1

select * 
from MyTable 
where ColumnDateFrom <= '2009-09-25' 
    and ColumnDateTo >= '2009-09-25'

Answer 2

if I understand correctly, try this:

SELECT
    *
    FROM MyTable 
    WHERE ColumnDateFrom <= '2009-09-25' AND ColumnDateTo >= '2009-09-25'

Answer 3

Try this:

SELECT * FROM MyTable WHERE '2009-09-25' BETWEEN ColumnDateFrom AND ColumnDateTo

Answer 4

mysql:

select * from MyTable where '2009-09-25' between ColumnDateFrom and ColumnDateTo

Answer 5

Standard Between should work (T-SQL).

SELECT * FROM MyTable WHERE @MYDATE BETWEEN ColumnDateFrom AND ColumnDateFrom

Answer 6

Just remove the "Is"

Select * From MyTable 
Where '2009-09-25' Between ColumnDateFrom to ColumnDateTo

remember to consider the time portiomn if the column values have date and time in them... (assuming DateOnly() is some function that strips the time from a datetime column)

Select * From MyTable 
Where '2009-09-25' Between DateOnly(ColumnDateFrom) 
                        To DateOnly(ColumnDateTo)