CODEWITHSUNDEEP
phpmysqldate
Heis
Heis

Reputation: 568

date(now) only working with time 00:00:00

I'm trying to get the number of rows where the date is today. Its working like this but only if the time is 00:00:00 so if i change the time to for example 2016-04-25 05:30:30 its not counting that row for some reason.

$result16  = $dbhandle->query("SELECT * FROM email  WHERE signoff = DATE(NOW() )");
$row_cnt16 = $result16->num_rows;

What should I do to fix this?

Table information:
signoff = varchar(250)

Outputs:

if date is 2016-04-25 00:00:00 the output is 2

if the date is 2016-04-25 05:04:00 output is 1

Upvotes: 0

Views: 63

Answers (3)

Osama Jetawe
Osama Jetawe

Reputation: 2705

Try this by adding Date to the signoff

$result16 = $dbhandle->query("SELECT * FROM email  WHERE DATE(signoff) = DATE(NOW() )");

Upvotes: 1

juergen d
juergen d

Reputation: 204784

First change your data type of signoff to datetime.

Then use this query

SELECT * FROM email  
WHERE date(signoff) = DATE(CURDATE())

If speed is an issue you can use 

SELECT * FROM email  
WHERE signoff >= curdate()
  AND signoff < curdate() + interval 1 day

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

try this:

SELECT *
FROM email 
WHERE signoff BETWEEN DATE(NOW()) AND  DATE(NOW() + INTERVAL 1 DAY)

Upvotes: 2

Related Questions