mysql date query

Question

my table look like this

Name     Created
ganesh   2010-06-25 10:54:54 
vasant   2010-06-25 11:54:54 
charlie  2010-06-25 12:54:54 
sam      2010-06-25 13:54:54 
vasamtj  2010-06-25 01:54:54 
jack     2010-06-25 02:54:54 
nima     2010-06-24 10:54:54 
kumar    2010-06-24 10:54:54 
jay      2010-06-24 10:54:54 
paala    2010-06-23 10:54:54 
test1    2010-06-23 10:54:54 
jhon     2010-06-22 10:54:54   

i want to show only today entries , in where condition i dont want to add time ,

SELECT response FROM ost_ticket_response WHERE staff_id = 1 AND created ='now()'

this query checking with time also,

Thanks

Answer 1

Your table information is not complete but this query will do what you need:

SELECT *
FROM ost_ticket_response
WHERE DATE_FORMAT(Created, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

Instead of DATE_FORMAT(NOW(), '%Y-%m-%d') you can just pass string value '2010-06-25' from your code

Answer 2

very rude, but:

SELECT response FROM ost_ticket_response WHERE staff_id = 1 AND created BETWEEN from_days(to_days(now())) AND from_days(to_days(now())+1)

Answer 3

WHERE Created >= date_format(CURRENT_TIMESTAMP(),'%Y%m%d000000')

Answer 4

THIS QUERY FIXED MY PROBLEM

SELECT count( response_id ) as cnt, staff_id FROM ost_ticket_response WHERE CAST( created AS Date ) = CURRENT_DATE( ) GROUP BY staff_id

THIS QUERY HAS ANY DRAWBACK ,

Answer 5

Why not? SELECT response FROM ost_ticket_response WHERE staff_id = 1 AND DATE(created) = CURDATE() ???