Problem with BETWEEN dates

Question

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

Then my sql got more or less like this:

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

However in the results it does not only return dates within the selected period, for example it returns date data 2017-02-20 and empty field that does not yet have its date set.

---

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 the fields, but the question is to return the dates within the period selected by the user. In case the user can select whether to search in data_inicial or data_final and in which order he would like to display the data.

Answer 1

You have 2 date fields, the BETWEEN will take the break in data_inicial the way you are doing it. If you want the interval between initial and final date, do it this way:

SELECT * FROM `negocio` WHERE data_inicial >= '2017-01-01' and data_final <= '2017-01-30' ORDER BY data_inicial DESC