MySQL, checking multiple dates between a range

Question

I have three date columns in a db row. The columns are independent of each other in that any of them can either be populated or NULL.

I'd like to check to find all rows where any of the date columns fall within a known range.

This is what I have in my WHERE clause:

WHERE (d.date1 OR d.date2 OR d.date3) 
    BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'

Any ideas as to why this isn't working would be appreciated.

Many thanks

Just out of curiosity, how would you select where column A or column B have the value 2? "where (A or B) = 2" or "where A = 2 or B = 2"? — cernunnos
– cernunnos, Commented Jun 4, 2013 at 13:31

Kermit · Accepted Answer · 2013-06-04 13:33:38Z

1

You are misunderstanding how logical "OR" works.

WHERE (d.date1 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46') 
   OR (d.date2 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46') 
   OR (d.date3 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46')

edited Jun 4, 2013 at 13:33

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

answered Jun 4, 2013 at 13:32

feathj

3,0792 gold badges24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Gibbs · Accepted Answer · 2013-06-04 13:33:26Z

0

You have to specify the three parts separately.

Note that (d.date1 OR d.date2 OR d.date3) will evaluate to true if any of the dates have a value, so your WHERE clause is currently the equivalent of this:

WHERE TRUE BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'

So you'll need to test each date separately:

WHERE d.date1 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'
   OR d.date2 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'
   OR d.date3 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'

answered Jun 4, 2013 at 13:33

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

1 Comment

Boomfelled Over a year ago

Great, thanks very much! This makes sense. However my the code I'm working with only allows me to use each of the known date variables once. So whilst the above is a logical outcome, I can't use it. I need some way of comparing each date against the range but only declaring the date range once. Thanks again.

Taylor Brown · Accepted Answer · 2013-06-04 13:33:56Z

0

You should be doing it like this:

WHERE (d.date1 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46')
OR (d.date2 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46')
OR (d.date3 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46')

answered Jun 4, 2013 at 13:33

Taylor Brown

1,7932 gold badges19 silver badges34 bronze badges

Comments

Charles Bretana · Accepted Answer · 2013-06-04 13:34:43Z

0

Because Between operator needs a single value to the left of Between. Reverse the sequence... like this:

WHERE d.date1 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46' Or
      d.date2 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46' Or
      d.date3 BETWEEN '2011-11-09 13:08:46' AND '2011-11-11 16:08:46'

answered Jun 4, 2013 at 13:34

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Collectives™ on Stack Overflow

MySQL, checking multiple dates between a range

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related