Problem when returning a query

Asked

Viewed 18 times

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 record on 02, however if I run the query until 02, it returns nothing?

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
  • 2

    Probably the field dadf331.datlan is datetime and has hours on the record. It should work if you do the restriction like this: ... AND dadf331.datlan <= '2018-04-02 23:59:59'

  • @Diegorafaelsouza really it is datetime, had not connected me, and its use DATE_FORMAT(datlan, '%Y/%m/%d') >= DATE_FORMAT('2018-04-02', '%Y/%m/%d')

  • That. It should work as well, but it does a little damage to performance, neh?!

  • 1

    @Diegorafaelsouza ah yes it worked, it is because I pass this date as parameter, through 2 inputs, and the input only sends the date

1 answer

1

You can use the DATE_ADD to contemplate the records that include time in your interval.

Then, you add a day at the end date of the interval (which comes without time) and search for the records prior to that new date.

Thus:

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.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.