Problem with BETWEEN between dates

1

I need a light for an issue, I have a data filtering system (search) where I have to search for two dates, Initial and Final. Since I have these two columns in the database, then when I execute the search I choose which column I want to filter. Both columns are of type date

So my sql looked something like this:

SELECT * FROM negocio WHERE data_inicial BETWEEN '2017-01-01' AND '2017-01-30' ORDER BY data_inicial DESC

But in the results it does not return only dates within the selected period, for example it returns data of the% data% and empty field that does not have its defined date.

CREATE TABLE negocio (
  negocio_id int(11) NOT NULL auto_increment,
  data_inicial date default NULL,
  data_final date default NULL,
  PRIMARY KEY  (negocio_id)
);

As I said are from the fields, but the question is to return the date period within the period selected by the user. In case the user can select whether to search in 2017-02-20 or data_inicial and in which order he would like to display the data.

    
asked by anonymous 19.04.2017 / 15:30

1 answer

4

You have 2 date fields, BETWEEN will make the range within data_inicial the way you are doing it. If you want the interval between start_date and end_date, do this:

SELECT * FROM 'negocio' WHERE data_inicial >= '2017-01-01' and data_final <= '2017-01-30' ORDER BY data_inicial DESC
    
19.04.2017 / 16:07