Problem returning a query

Question

Problem returning a query

Navigation

#1 by (1 votes)

0

Why when I run the query below, it returns records:

SELECT COUNT(DISTINCT(dadf332.numped)) AS qtdped,   
DATE_FORMAT(dadf331.datlan, '%d/%m/%Y') AS datavenda 
FROM dadf331, dadf332 
WHERE dadf331.numped =  dadf332.numped
AND tipped = 0 
AND dadf331.datlan >= '2018-04-01' 
AND dadf331.datlan <= '2018-04-03' 

qtdped  datavenda   
------  ------------
24      02/04/2018

It returns a record on day 02, but if I run the query until day 02, it does not return anything?

SELECT COUNT(DISTINCT(dadf332.numped)) AS qtdped,
DATE_FORMAT(dadf331.datlan, '%d/%m/%Y') AS datavenda
FROM dadf331, dadf332 
WHERE dadf331.numped = dadf332.numped 
AND tipped = 0
AND dadf331.datlan >= '2018-04-01' 
AND dadf331.datlan <= '2018-04-02'

qtdped  datavenda  
------  -----------
     0  (NULL)

mysql sql

asked by anonymous 13.06.2018 / 15:02

1 answer

Select the two largest values of a vector and add them? MySQL SELECT with ORDER BY in GROUP BY

score 1 · Answer 1

You can use DATE_ADD to view records that include time in their range.

Then, you add a day to the end date of the range (which comes with no time) and searches the records before this new date.

So:

SELECT COUNT(DISTINCT(dadf332.numped)) AS qtdped,
    DATE_FORMAT(dadf331.datlan, '%d/%m/%Y') AS datavenda
FROM dadf331, dadf332 
WHERE dadf331.numped = dadf332.numped 
    AND tipped = 0
    AND dadf331.datlan >= '2018-04-01' 
    AND dadf331.datlan < DATE_ADD('2018-04-02', INTERVAL 1 DAY)

I hope it helps.