Geek asks and answers

MySQL: correct 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 managed to get the result set if the start date started exactly by the value in the field.

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

Now the problem is that I am pointing 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 s)

NO RESULT. How can I get the date range between different fields of SQL?

Any help is appreciated.

+3
source share
3 answers

You have your own logic back. If you want the seasons in which the specified date was in that season, your where clause should look like this:

WHERE '2014-01-06' BETWEEN start AND end;
+5
source

, ( SQL Fiddle):

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

When asked if a specific date is in a given range, the sentence is WHEREusually this:

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

Otherwise, this is an illogical date range for MySQL and returns an empty result set.

The comparison operator is BETWEENequivalent to:

min <= expr And expr <= max

Therefore, it should have been used as follows:

expr BETWEEN min AND max

Here is the documentation from MySQL on the BETWEEN Operator Comparison .

+1
source

More articles:


All Articles