MySQL: Proper date range between start and end date as two fields

Question

I have the following table called seasons

+----+--------+------------+------------+
| id | cost   | start      | end        |
+----+--------+------------+------------+
| 33 |    255 | 2014-01-05 | 2014-04-16 |
| 17 |    357 | 2014-04-17 | 2014-04-19 |
| 65 | 191.25 | 2014-04-20 | 2014-07-10 |
| 49 |    255 | 2014-07-11 | 2014-08-23 |
| 81 | 191.25 | 2014-08-24 | 2014-12-18 |
+----+--------+------------+------------+

I am trying to get a date range between start and end using the following query.

SELECT 
    *
FROM
    seasons
WHERE
    (start BETWEEN '2014-01-05' AND '2014-01-05' OR end BETWEEN '2014-01-05' AND '2014-01-05');

I was able to get a result set if the start date started exactly on the value on the field.

+----+------+------------+------------+
| id | cost | start      | end        |
+----+------+------------+------------+
| 33 |  255 | 2014-01-05 | 2014-04-16 |
+----+------+------------+------------+

Now the problem is when I advance the date to

2014-01-06

SELECT 
    *
FROM
    seasons
WHERE
    (start BETWEEN '2014-01-06' AND '2014-01-06' OR end BETWEEN '2014-01-06' AND '2014-01-06');

Empty set (0.00 sec)

There are NO RESULT. How can I get the date range in between to different fields on SQL?

Any help is appreciated.

This has also been discussed here. http://stackoverflow.com/questions/3822648/mysql-query-between-two-dates — Derek W, Feb 18 '14 at 14:50

score 6 · Accepted Answer · answered Feb 18 '14 at 14:42

6

You have your logic backward. If you want seasons where a given date is in that season then your where clause should look like:

WHERE '2014-01-06' BETWEEN start AND end;

answered Feb 18 '14 at 14:42

Jim

22,354
6
52
80

No sh*t. This worked! Can you explain briefly why is it backward? Thanks – Louie Miranda Feb 18 '14 at 14:46
@LouieMiranda Certainly. Your original logic was checking for seasons with start days or end days between `'2014-01-06'` and `'2014-01-06'`. This is the same as saying `WHERE start = '2014-01-06' OR end = '2014-01-06'`. What you really wanted was any season where the date was inside that seasons start and end dates. – Jim Feb 18 '14 at 14:49

score 2 · Answer 2 · answered Feb 18 '14 at 14:48

2

Since you are not really looking for a range and only needing a specific date, you could also just use (SQL Fiddle):

SELECT *
FROM seasons
WHERE start = '2014-01-05' OR end = '2014-01-05'

answered Feb 18 '14 at 14:48

Linger

14,942
23
52
79

Derek W · Answer 3 · 2014-03-05T01:21:00.797

When querying if a certain date is in a given range, the WHERE clause typically is:

WHERE 'specified_date' BETWEEN 'start_date' AND 'end_date'

Otherwise it is an illogical date span to MySQL and will return an empty result set.

The BETWEEN Comparison Operator is equivalent to:

min <= expr AND expr <= max

Therefore, it's meant to be used like this:

expr BETWEEN min AND max

Here is documentation from MySQL on the BETWEEN Comparison Operator.

MySQL: Proper date range between start and end date as two fields

3 Answers3