mysql with the date function

Question

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?

See also stackoverflow.com/questions/3412735/…, more examples there — Pekka
– Pekka, Commented Aug 5, 2010 at 11:18

aviv · Accepted Answer · 2010-08-05 11:19:36Z

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);

answered Aug 5, 2010 at 11:19

aviv

2,7947 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DrColossos · Accepted Answer · 2010-08-05 11:28:25Z

1

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

edited Aug 5, 2010 at 11:28

answered Aug 5, 2010 at 11:22

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

Comments

Jim · Accepted Answer · 2014-01-20 16:23:12Z

1

You can try this one method

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

edited Jan 20, 2014 at 16:23

Jim

22.7k6 gold badges55 silver badges81 bronze badges

answered Aug 5, 2010 at 11:52

user395702

1066 bronze badges

Comments

eriktm · Accepted Answer · 2010-08-05 11:25:22Z

0

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 | 
+-----------------+

answered Aug 5, 2010 at 11:25

eriktm

7821 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

mysql with the date function

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related