5

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

Improve this question
1
  • 1
    And which particular database engine is this? And how are you going to pass the date to the SQL, as an embedded literal (ie. as part of the SQL) or as a parameter? And what client framework are you using to talk to the database? What is basically your question? The SQL syntax? How to write it in C# or php or whatever? or what? Because, idea-wise, you're right on, except the syntax for writing the date is off (but I need to know which database engine you're using to tell you what is right). Commented Sep 25, 2009 at 19:20

7 Answers 7

Reset to default
6

Standard Between should work (T-SQL). 

SELECT * FROM MyTable WHERE @MYDATE BETWEEN ColumnDateFrom AND ColumnDateFrom
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

if I understand correctly, try this:

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

3 Comments

Nice, but I like better... select * from MyTable where '2009-09-25' between ColumnDateFrom and ColumnDateTo
Thank you very much for you quick response...VERY NICE! I am now done and can go home... Wish you all a nice weekend!
I never use BETWEEN, I find it easier to read >=, >, <=, and/or < because you know if the end points are included, with BETWEEN you have to remember if the end points are included or not.
4

Try this:

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

Comments

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

Comments

0

mysql:

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

Comments

0

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)
Improve this answer

1 Comment

This function call on the date column is going to prevent any index use is it not?
0

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\#")
Improve this answer

Comments