How to Change the Format of MySQL Dates Using PHP

Dates stored in MySQL databases are represented using the format ‘YYYY-MM-DD’. People don’t usually say they accomplished a task on 2013-01-01, so this format is not the most convenient. Often we need to convert the format of a date stored in a database into a more readable or more user-friendly format.

We can use PHP to re-format dates retrieved from a MySQL database using two functions, DateTime() and date_format().

Note: One requirement for the DateTime() function to work is that the date column in the database must be of the type datetime, not date. Even if the date column started life with the type date, it can be changed at any time. Just realize that dates will now be reported along with the time, which will be all zeroes in this case. To make the date look pretty again, use both functions as outlined below.

A warning is reported when a date column is of the type date when a function expects the type datetime. You might see something like “Warning: date_format() expects parameter 1 to be DateTime, string given” if this happens to you.

Once the dates are recorded as the type datetime, using the date_format() function of PHP will work. Going back to values that are already stored in a database and updating the type is ok, this doesn’t have to happen at the creation of the database. So, if you’re keeping track of dates and want to use a format other than something like “2013-01-01”, keep reading. Continue reading How to Change the Format of MySQL Dates Using PHP