Search Date (now) in Specific time-CodePudding

I want to search a Date (now) like 2022-04-11 in specific time like 00:00:00. and in this code showing date & time 0000-00-00 00:00:00.

SELECT Process_Date, Product_Number, COUNT(*) AS TEST FROM tb_main WHERE Process_Date = DATE_FORMAT(NOW(), '00:00:00') GROUP BY Product_Number;

i've already tried this code but syntax was error.

CURDATE(), '00:00:00'

CodePudding user response：

If you want to use DATE_FORMAT to get midnight of the current date, then you need a format mask for the entire timestamp:

SELECT Process_Date, Product_Number, COUNT(*) AS TEST
FROM tb_main
WHERE Process_Date = DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
GROUP BY Product_Number;

You could also use the TIMESTAMP function here:

SELECT Process_Date, Product_Number, COUNT(*) AS TEST
FROM tb_main
WHERE Process_Date = TIMESTAMP(CURRENT_DATE)
GROUP BY Product_Number;