search date range

[viveleroi0]

I have a mysql field with a date format of 11/16/2002, how would I create a query/loop that allows a user to search a range of dates? Like 11/01/2002 to 11/16/2002?

I have the mysql field as a text field... do I need to change it do date or something?

[hand]

Yes, please change the format
http://www.mysql.com/doc/en/DATETIME.html

Advantage: http://www.mysql.com/doc/en/Date_and_time_functions.html

[viveleroi0]

So I suppose that I need to change form 11/16/2002 to 2002-11-16, and thats fine. I don't mind doing that, but I do not want the customers of the website to see dates displayed as 2002-11-16, I would like to find a way to convert the database result from 2002-11-16 to 11/16/2002. Is there a combination of explode/loop or something thaty can do this?

[hand]

You can convert it during the select statement

mysql> select datum from a;
+------------+
| datum      |
+------------+
| 2002-01-01 |
+------------+
1 row in set (0.00 sec)

mysql> select date_format(datum,"%m/%d/%y") as datum from a;
+----------+
| datum    |
+----------+
| 01/01/02 |
+----------+
1 row in set (0.00 sec)

mysql> select date_format(datum,"you can format %m!%d as u want /%y") as datum from a;
+------------------------------------+
| datum                              |
+------------------------------------+
| you can format 01!01 as u want /02 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(datum,"%m/%d/%Y") as datum from a;
+------------+
| datum      |
+------------+
| 01/01/2002 |
+------------+
1 row in set (0.00 sec)

http://www.mysql.com/doc/en/Date_and_time_functions.html