Better date time display using MySQL query

An article by admin    No Comments

So, you’ve got your database entries entered, optimized, and in the MySQL friendly 0000-00-00 format- but database date formatting just isn’t doing it for you on output?

Using a quick trick within a MySQL query, we can easily convert that boring date into something a little more friendly and readable.

Adding the “DATE_FORMAT” parameter to your query allows you to specify the conversion, leaving nothing for you to do except use your php to output.

“SELECT DATE_FORMAT(your_date_column_here, ‘%M %D, %Y at %l:%i %p’) FROM your_database_table_here” will modify your output to read “June 6th, 2011 at 9:53 PM”.

What if you need to get more than just the date with your query? No problem:

“SELECT *,DATE_FORMAT(your_date_column_here, ‘%M %D, %Y at %l:%i %p’) AS date FROM your_database_table_here”

By appending the ‘*,’ operator (or specifying the columns to retrieve data from) to the query, we can easily continue to specify our nicely formatted date in addition to other variables, though note that in the second example (above)- we’ve modified the DATE_FORMAT request to specify it will be output as “date” in order to provide a distinction between the date field, and other queried fields.

More information on MySQL date formatting can be found here.