Compare a range of dates with BETWEEN in mysql

Question

Compare a range of dates with BETWEEN in mysql

Navigation

#1 by (3 votes)
#2 by (-1 votes)

2

I am creating a module in a system with PHP and MySQL, where I am making an agenda for events. I have a table with the following fields:

id_evento
nombre_evento
fechaInicial
fechaFinal
id_ubicacion

In the creation form for the agenda, the user has two input of type date , where he enters the initial date and the final date of his event, suppose that I have registered an event with fechaIncial = '2016-02-18' and fechaFinal = '2016-02-22' .

If the user enters fechaIncial = '2016-02-19' and fechaFinal = '2016-02-19' as valid it is already registered an event between 18 and 22, which is where it would enter on the 19th.

I currently have this query but I'm not sure how it works because it only validates me OR

SELECT id_evento,nombre_evento,id_ex_ubicacion,fecha_ini_evt,fecha_fin_evt,hora_ini_evt‌,hora_fin_evt
FROM eventos
WHERE fecha_ini_evt >= '2016-02-19'
    AND fecha_fin_evt <= '2016-02-19'
        OR fecha_fin_evt BETWEEN '2016-02-19' AND '2016-02-19'
        OR fecha_ini_evt BETWEEN '2016-02-19' AND '2016-02-19'
    AND id_ex_ubicacion = 1

php mysqli

asked by jvieyra 19.02.2016 в 17:35

source

2 answers

-1

This validates me if the initial and final range of an hour, if there is an answer this will get me a result

  SELECT * FROM 'horario' WHERE inicio <= '17:00' and final >= '22:00' AND 
  clinica_IDclinica='2' OR inicio BETWEEN '17:00' AND '22:00' AND 
  clinica_IDclinica='2' OR final  BETWEEN '17:00' AND '22:00' AND 
  clinica_IDclinica='2'

answered by 27.05.2017 в 05:35

Error with the Django runserver command Choose recipient's mail in a random way in contact form with php

score 3 · Accepted Answer

Apparently your problem is the use of parentheses. When you use a statement OR you should always keep in mind to use them, but your query will bring all the possible data for the validations before and after the OR .

Your query should look like this:

SELECT id_evento,nombre_evento,id_ex_ubicacion,fecha_ini_evt,fecha_fin_evt,hora_ini_evt‌,hora_fin_evt
FROM eventos
WHERE ((fecha_ini_evt <= '2016-02-19'
    AND fecha_fin_evt >= '2016-02-19')
        OR fecha_fin_evt BETWEEN '2016-02-19' AND '2016-02-19'
        OR fecha_ini_evt BETWEEN '2016-02-19' AND '2016-02-19')
    AND id_ex_ubicacion = 1