How to check events between 2 dates in MySQL?

Navigation
19

Knowing that I have a table called eventos , and this table has the columns id , titulo , inicio and fim . The inicio and fim columns are of type timestamp , where the start and end date and time of a certain event is written. When I make a% of of this table, how do I get the events of a specific date that is between the date of the start column and the end column?

Example: Assuming I have a record in this table with the start column having the value select and the end column with value 2014-02-01 13:00:00 . It is an interval of 4 days, and their respective hours. How could I get this record on a specific day using the following SQL below? 

SELECT titulo
FROM eventos
WHERE inicio <= '2014-02-03'
AND fim >= '2014-02-03'

One of the problems is that if my registry is with the start column with value 2014-02-05 22:30:00 and I query for 2014-02-01 13:00:00 , that is, the same start date, it is not found. Knowing that I have the WHERE inicio <= '2014-02-01' operator.

    
asked by anonymous 04.02.2014 / 20:30

4 answers

15

Use the BETWEEN operator to facilitate and convert DATETIME to DATE .

Example: 

SELECT titulo
FROM eventos
WHERE '2014-02-01' BETWEEN date(inicio) AND date(fim)

See the example in sqlfiddle .

Mysql% date() function extracts only the part of the date to ignore the time in the comparison.

If you want to do this without BETWEEN : 

SELECT titulo
FROM   eventos
WHERE  '2014-02-01' >= date(inicio)
  AND  '2014-02-01' <= date(fim)

See the sqlfiddle .

    
04.02.2014 / 20:42
2

If you compare the start and end of a range to a single date, then an alternative to the query is to use BETWEEN , for example: 

SELECT titulo
FROM eventos
WHERE '2014-02-03' BETWEEN DATE(inicio) AND DATE(fim)
    
04.02.2014 / 20:40
0

You're having the same problem as mine, BETWEEN in some banks does not bring the exact same day value, try and >= e <= in the bank is the same as BETWEEN , put one more day in your search for eg: 

SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-04'
    
20.08.2015 / 13:41
-5

You missed the logic I imagine, the opposite would be the opposite of the operators: 

SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-03'
    
04.02.2014 / 20:33
jQuery dataTable sAjaxSource - Show error while doing search Data request, checkout Sky