CODEWITHSUNDEEP
mysqlbetweendate-range
Louie Miranda
Louie Miranda

Reputation: 1159

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

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.

Upvotes: 3

Views: 2647

Answers (3)

Derek W
Derek W

Reputation: 10026

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.

Upvotes: 1

Linger
Linger

Reputation: 15048

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'

Upvotes: 2

Jim
Jim

Reputation: 22646

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;

Upvotes: 6

Related Questions