CODEWITHSUNDEEP
mysqlsqldateselect
nielsv
nielsv

Reputation: 6810

SELECT between today and yesterday

I'm trying to select between two dates like this:

SELECT p.Code, p.Name, sum(h.PA = 1) AS PA, sum(h.PB = 1) AS PB, 
    sum(h.PG = 1) AS PG, sum(h.GoedkeuringDoorNew = 'GF') AS GF, 
    sum(h.GoedkeuringDoorNew = 'SB') AS SB, sum(h.GoedkeuringDoorNew = 'VIA') AS VIA,
    sum(h.Blanco) AS Blanco
FROM psthostess p 
LEFT JOIN `psttodo-uit` h 
    ON h.`Hostess Code` = p.Code 
    AND DATE(h.`afgewerkt tablet datum`) BETWEEN CURDATE() 
    AND (CURDATE() - INTERVAL 1 DAY)
WHERE p.Indienst = 1 GROUP BY p.Code, p.Name

But I don't get any results.

I want the results of today and yesterday. But when I do this

SELECT *
FROM `psttodo-uit` p
WHERE DATE(p.`afgewerkt tablet datum`) = CURDATE() - INTERVAL 1 DAY

or change CURDATE() - INTERVAL 1 DAY into CURDATE() I get results... How does this come?

Upvotes: 3

Views: 9521

Answers (2)

KarthikManoharan
KarthikManoharan

Reputation: 805

 Select *  from  emp where joindate between SUBDATE(NOW(),1) and NOW();

Upvotes: 0

John Woo
John Woo

Reputation: 263723

WHERE p.`afgewerkt tablet datum` >= CURDATE() - INTERVAL 1 DAY 
      AND p.`afgewerkt tablet datum` < CURDATE() + INTERVAL 1 DAY

Assuming current date and time is: April 11, 2014 05:30PM, the datetime you need is between
April 10, 2014 00:00:00 and April 11, 2014 23:59:59 (inclusive)

CURDATE() - INTERVAL 1 DAY => April 10, 2014 00:00:00
CURDATE() + INTERVAL 1 DAY => April 12, 2014 00:00:00

Upvotes: 10

Related Questions