Michael Cooper Michael Cooper - 3 months ago 10
SQL Question

SQL get records from specific day

i am looking to only get records from exactly 3 days ago, not the records from now and as far back as 3 days ago... i only want records for that 24hour period and when i use the > its selecting everything from now until then

this is my query

SELECT user_id, nickname, user_email, date_of_register, verification_code, user_photo_url FROM `tbl_users` WHERE verified='N' AND date_of_register > DATE_SUB(CURDATE(), INTERVAL 3 DAY) ORDER BY date_of_register ASC


column date_of_register is
date time
and this is the format

2016-08-26 08:57:52


thank you for your help

Answer

Compare dates instead of date and time, CAST your date_of_register as DATE only.

WHERE verified='N' AND 
      CAST( date_of_register AS DATE) = CAST(DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS DATE)