mysql with the date function

Question

Hi I have a mysql table containing a field name dtt_date and have values like 08/04/2010 22:15:00. I want to display all the records with in this month (08, august), How to write a mysql query in my php page to display these record. Does any one know this?

Please help me?

Answer 1

  SELECT * FROM table WHERE dtt_date>='2010-08-01' AND dtt_date<='2010-08-31';

In PHP:

 $q = "SELECT * FROM table WHERE dtt_date>='2010-08-01' AND dtt_date<='2010-08-31'";
 $res = mysql_query($q);
 while($row = mysql_fetch_assoc($res))
   var_dump($row);
 mysql_free_results($res);

Answer 2

Untested, I'm sure there are easier methods to this. Not sure if your date format will be handeled by MySQL

SELECT * FROM table WHERE MONTH(DATE_FORMAT(date,'%Y-%m-%d')) = 8

Answer 3

You should change the date format in the table to be '2010-08-04 22:15:00', then you could run this query:

SELECT DATE_FORMAT(dtt_date, '%D %M %Y') as date FROM myTable

From there you would get something of this as a result, and then you can experiment with the date formatting.

+-----------------+
| date            |
+-----------------+
| 4th August 2010 | 
+-----------------+

Answer 4

You can try this one method

SELECT * FROM table WHERE month(dtt_date)='08' AND year=(dtt_date)='2010';