How to obtain data between a range of dates using MySQL?

Navigation
0

I have a question and it is that I want to make a query between two dates by consulting the same column that is Fecha , the following query does me that search: 

SELECT *
FROM   view_listservicios a
       JOIN rel_franquiciaslavadores b
         ON a.idlavador = b.idlavador
WHERE  a.estatus = 'Finalizado'
       AND fecha >= '2017-02-01'
       AND a.fecha <= '2017-02-09'
ORDER  BY idtab_serviciolavado ASC

On the contrary, if I apply that same query but using the same day Fecha >= '2017-02-09' AND Fecha <= '2017-02-09' does not return anything.

I have already used BETWEEN but still I do not get the desired result.

    
asked by Juan Carlos Garcia 10.02.2017 в 17:38
source

4 answers

1

You may very well miss the hours. To make the query between these two dates. 

SELECT *
FROM   view_listservicios a
       JOIN rel_franquiciaslavadores b
         ON a.idlavador = b.idlavador
WHERE  a.estatus = 'Finalizado'
       AND fecha >= '2017-02-01 00:00:00'
       AND a.fecha <= '2017-02-09 23:59:59'
ORDER  BY idtab_serviciolavado ASC
    
answered by 10.02.2017 / 17:52
source
2

In cases of columns of data type DATETIME , it is always important to consider the time. When you type 2017-02-09 , by not specifying the time you're really saying 2017-02-09 00:00:00.00 .

In this way, it is easy to see that: 

Fecha >= '2017-02-09 00:00:00.00' 
AND Fecha <= '2017-02-09 00:00:00.00'

It's the same as saying: 

WHERE Fecha = '2017-02-09 00:00:00.00'

What is very easy that does not give you results. I recommend that in these cases use less strict than the next day instead of <= : 

SELECT *
FROM   view_listservicios a
       JOIN rel_franquiciaslavadores b
         ON a.idlavador = b.idlavador
WHERE  a.estatus = 'Finalizado'
       AND fecha >= '2017-02-01'
       AND a.fecha < '2017-02-10'
ORDER  BY idtab_serviciolavado ASC
    
answered by 10.02.2017 в 18:07
2

You could use DATE() to convert to date the value of the column datetime .

So for example: 

SELECT *
FROM   view_listservicios a
   JOIN rel_franquiciaslavadores b
     ON a.idlavador = b.idlavador
WHERE  a.estatus = 'Finalizado'
   AND DATE(a.date) BETWEEN '2017-02-01' AND '2017-02-09'
    
answered by 10.02.2017 в 18:14
1

Why if you are using the Date field of the same table in a yes the references and in which it returns nothing, not the references? If in both tables you have a Date field there is ambiguity and maybe that's why you do not return anything. 

AND Fecha >= '2017-02-01' AND a.Fecha <= '2017-02-09' //Sí regresa
AND Fecha >= '2017-02-01' AND Fecha <= '2017-02-09' //No regresa

I would have liked to comment on that instead of posting it as an answer but I still do not have the necessary reputation.

    
answered by 10.02.2017 в 17:44
Close a Java program at any time Play a GIF Image on a JPanel without using URL (JAVA)