Formatting a date in mysql

For many years, I worked with MySQL on a pretty basic level, I carried out inserts, updates and deletes, and that’s about it. I think it might be beneficial (if anyone even reads this thing) to do a series of posts on some of the things that I was not aware of when I started off, that might be of help to others.

One of the staples in my classes folder was a function that takes a date from MySQL (In any of the relevant formats, usually datetime though) and then formats it (Using substr() or strtotime() and date()) with PHP into a nice human readable format for display on the website.

There is a more elegant solution however.

MySQL has a number of functions in place for dealing with dates and times so you can return the nicely formatted date direct from the database!

Most importantly for this example we have DATE_FORMAT.

In your database, you have a table news_stories that has a field date with a type of ‘date’

SELECT DATE_FORMAT(date, ‘%M %D, %Y’) FROM `news_stories` WHERE 1

This will return a nice easy to read date (Such as 29th June, 2008) in your result set.