MySQL select date BETWEEN syntax prints wrong results

Question

I have this long and nasty MySQL query:

SELECT * FROM
    studentdates sd
        INNER JOIN
    students s ON s.student_id = sd.student_id
        INNER JOIN
    cityselections cs ON cs.city_id = s.student_city_id
        INNER JOIN
    customers c ON c.customer_id = s.student_customer_id
        INNER JOIN
    price_to_course_number ptcn ON ptcn.ptcn_course_number = sd.student_course_number
        INNER JOIN
    pricegroups pg ON pg.price_id = ptcn.ptcn_price_id
WHERE
    ('2012-10-01' OR '2012-10-31' BETWEEN sd.student_startdate AND sd.student_enddate)
        AND cs.city_id = '12'
        AND sd.student_course_number = '46248'
ORDER BY s.student_lastname ASC

This will print the following lines (edited out unnecessary columns):

+------------+--------------------+-----------------+
| student_id | student_startdate  | student_enddate |
+------------+--------------------+-----------------+
| 299        | 2012-09-24         | 2012-10-21      |
| 299        | 2012-09-17         | 2012-09-23      |
+------------+--------------------+-----------------+

Notice in my query I have WHERE ('2012-10-01' OR '2012-10-31' BETWEEN sd.student_startdate AND sd.student_enddate)

Why do I get this as a result?

| 299 | 2012-09-17 | 2012-09-23 |

Does it work if you do 2 between. 1 for 2012-10-01 and 1 for 2012-10-31? — Marc
– Marc, Commented Nov 5, 2012 at 13:03

lc. · Accepted Answer · 2012-11-05 13:06:37Z

1

AND and OR are used to connect Boolean expressions. In your case, you have the following expression:

'2012-10-01' OR '2012-10-31' BETWEEN sd.student_startdate AND sd.student_enddate

This has two parts (joined by an OR relationship):

'2012-10-01'
'2012-10-31' BETWEEN sd.student_startdate AND sd.student_enddate

The first part will always evaluate to true (it is non-zero, non-null), therefore the result of this expression will also always be true.

Did you perhaps mean this instead?

sd.student_enddate >= '2012-10-01' AND sd.student_startdate <= '2012-10-31'

answered Nov 5, 2012 at 13:06

lc.

117k21 gold badges163 silver badges190 bronze badges

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

5 Comments

David Over a year ago

Thank's alot for giving me a detailed explanation. It work's like a charm now.

AnandPhadke Over a year ago

@ls-whats the difference between urs query and mine below.?

lc. Over a year ago

@AnandPhadke It looks like you have the start and end columns swapped. Your query will return rows contained within October, whereas this one will return rows which have any part of the date period in October. Since it looked like the OP wanted to keep the row with dates 9/24-10/21, I assumed this was the one he was looking for and was apparently correct.

AnandPhadke Over a year ago

OP needs data between '2012-10-01' and '2012-10-31' rite?

lc. Over a year ago

@AnandPhadke It would appear the OP needs data crossing into October 2012.

AnandPhadke · Accepted Answer · 2012-11-05 13:06:19Z

1

SELECT * FROM
    studentdates sd
        INNER JOIN
    students s ON s.student_id = sd.student_id
        INNER JOIN
    cityselections cs ON cs.city_id = s.student_city_id
        INNER JOIN
    customers c ON c.customer_id = s.student_customer_id
        INNER JOIN
    price_to_course_number ptcn ON ptcn.ptcn_course_number = sd.student_course_number
        INNER JOIN
    pricegroups pg ON pg.price_id = ptcn.ptcn_price_id
WHERE
    sd.student_startdate >='2012-10-01' and  sd.student_enddate <='2012-10-31' 
        AND cs.city_id = '12'
        AND sd.student_course_number = '46248'
ORDER BY s.student_lastname ASC

answered Nov 5, 2012 at 13:06

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

1 Comment

AnandPhadke Over a year ago

Then I guess there is no data for the specific values in where clause

Collectives™ on Stack Overflow

MySQL select date BETWEEN syntax prints wrong results

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related