How to play with Date

[DrTrans]

print "<center> Choose a Date: </center>";
   print "	<form action=\"\" method=\"POST\">
<p align=\"center\">
       <select name=\"daterange\">
";
        getconnect();
        $active = "1";
        $query = "SELECT * FROM accounting GROUP BY DATE_FORMAT(payment_date , '%m %d, %Y,') ASC";
        $result = mysql_query($query);
        while($row = mysql_fetch_assoc($result))
	{
         $payment_date = $row['payment_date'];
       print "<option value=\"$id\"> $payment_date </option>";
   }
       print "</select>";

 

 

I want to be able to select each day from drop down menu

 

ex. Date in Database is Jul 1, 2012 04:15

ex. Date in Database is Jul 2, 2012 03:15

ex. Date in Database is Jul 2, 2012 05:15

 

Want it to group it by Month, Day, Year only. 

 

Thanks in advance.

 

 

 

 

[Pikachu2000]

So what seems to be the problem, exactly?

[DrTrans]

Its not grouping them by date ( Month, Day). it gives me a single date.

 

I have months and months of entrys. I want to be able to pull a group of entries by date

[Pikachu2000]

Are the values actually stored in Jul 1, 2012 04:15 format in the database?

[DrTrans]

I just changed them to

 

Jun 5, 2012
Jun 4, 2012
Jul 21, 2012

 

etc

[Pikachu2000]

DATE_FORMAT() is choking on that, and returning NULL, so all your values are being grouped by that. Dates and times need to be stored in YYYY-MM-DD hh:mm:ss format, in a DATETIME (or TIMESTAMP) field. Dates alone should be stored in a DATE field. Then all of the built-in date and time functions in MySQL will work with them directly.

 

You can either convert them, and store them properly from here on out, or you can hack something together using STR_TO_DATE() to convert the date to a usable format before DATE_FORMAT() gets it. I'd recommend the former.

[DrTrans]

Ive fubar'd my entire database now.

 

 

[DrTrans]

When I store date, I'm using

 

$current_date = date("M j, Y");

 

however if having DATE in the MYSQL database in function. does not work.

 

Completely fubar'd

[Pikachu2000]

What did you do that fubar'd it?

[DrTrans]

I changed the structure type to "DATE"  .... not much of a problem. i can put the dates back...

 

My concern now is how do i now store them correctly.

[Pikachu2000]

OK. You can insert the date using the MySQL function CURDATE(), or change your php date() function to use the 'Y-m-d' formatting string. Then when you retrieve the date, you can use the MySQL DATE_FORMAT() function to format it for display, but you won't need to change the format to for ORDER BY or GROUP BY.

 

So similar to this, modified to use your field name, and preferred formatting string for the output:

SELECT DATE_FORMAT(date_field, '%m-%d-%Y') AS f_date FROM table GROUP BY date_field ORDER BY date_field DESC

[DrTrans]

Got it!! However, One more question.. then i won't bother you..

 

Is there a way to convert the current dates in mass to the Y-M-D format?

 

so i don't have to convert these all by hand? this sucks.

[Pikachu2000]

Yes. Backup/export the table if the data is important. Add a new field to the table, with a DATE type, then you run an UPDATE query to populate the new field with the converted date from the original field. You may have to play with the formatting string, but this should be pretty close.

 

UPDATE table SET new_date_field = STR_TO_DATE( old_date_field, '%b %e, %Y' )

 

After you run the UPDATE, inspect the data to make sure it went as planned. If it did, you can rename the old field, and give the new field the old field's name, as long as you've updated the other code to insert the date in the proper format. I say to rename rather than delete the old field right away in case you find issues later on. Once everything is tested and verified, you can delete the old field if you want to.

[DrTrans]

Thanks for the help. I owe you one!!!