CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Rune Brattas
Rune Brattas

Reputation:

Select records between two dates in two columns

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

Upvotes: 5

Views: 35405

Answers (7)

mdli
mdli

Reputation: 1

here example for ms access vba

"[LearningBegin]<= " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd]>=" + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + _
" or [LearningBegin]>= " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd]<=" + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + _
" or [LearningBegin] between " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and " + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd]<=" + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + _
" or [LearningBegin] between " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and " + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd]>=" + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + _
" or [LearningBegin] >= " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd] between" & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and " + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#") + _
" or [LearningBegin] <= " & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and [LearningEnd] between" & Format(eFilterBegin.Value, "\#mm\/dd\/yyyy\#") + " and " + Format(eFilterEnd.Value, "\#mm\/dd\/yyyy\#")

Upvotes: 0

e11s
e11s

Reputation: 4143

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

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

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)

Upvotes: 0

Quintin Robinson
Quintin Robinson

Reputation: 82335

Standard Between should work (T-SQL). 

SELECT * FROM MyTable WHERE @MYDATE BETWEEN ColumnDateFrom AND ColumnDateFrom

Upvotes: 6

Anatoliy
Anatoliy

Reputation: 30073

mysql:

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

Upvotes: 0

Yannick Motton
Yannick Motton

Reputation: 35971

Try this:

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

Upvotes: 4

KM.
KM.

Reputation: 103587

if I understand correctly, try this:

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

Upvotes: 4

Related Questions