-2

i have table messages with column date (format DATETIME) how i can search messages where date = xxxx-05-09 ? 

select * from messages where date = 'xxxx-05-09';
karpengold
  • 7
  • 1
  • 7
  • Possible duplicate of [How to select date from datetime column?](http://stackoverflow.com/questions/1754411/how-to-select-date-from-datetime-column) – anujm May 28 '16 at 12:49

3 Answers3

1

Try this: 

 select * from messages where Extract(Month from date) = 5 and Extract(Day from date) = 9;
Priyansh Goel
  • 2,660
  • 1
  • 13
  • 37
  • 15:51:16 select * from messages where Extract(Month from date) == 5 and Extract(Day from date) == 9 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 5 and Extract(Day from date) == 9' at line 1 0.000 sec – karpengold May 28 '16 at 12:53
  • @karpengold I had by mistake put == instead of = . Try it now – Priyansh Goel May 28 '16 at 12:56
0

You can also use DATE_FORMAT function:

select * from messages where DATE_FORMAT(date, "%Y-%m-%d") = 'xxxx-05-09';

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

nowaja
  • 79
  • 4
0

DATE function can help

SELECT * FROM messages WHERE MONTH(date) = '05' AND DAY(date) = '09';
Sajan Sharma
  • 109
  • 5