[toc]This tutorial explains how to format a MySQL date field to suit your needs without using PHP or any other post-processing.
For example, you can output your dates in a long format such as Monday, 12th May 2016 or as a short date format – 17/Dec/86. MySQL provides you with the function to do it and it’s really easy to use.
Most of the time you’ll store dates in your database using the DATE, DATETIME or TIMESTAMP field types. These datatypes store the date in the format:
This is handy for the database engine as it’s really easy to sort the date when the year is followed by the month and then the day.
This might be great from a logical point of view but people are more used to reading dates in their regional formats or other popular variants.
Some example date formats
|Long||Thursday, 12th May 2016|
The dates above all represent the same date but look very different in their output.
Sometimes, when you make a selection from your MySQL database, you want the date to be in something other than how MySQL represents it so that it’s more meaningful to your users.
The date parameter can be a date in database format or the name of your column. The format is a string made up of the placeholders described on MySQL’s manual page (click on the function above to view it).
A couple of example of DATE_FORMAT in a SELECT statement
Imagine that I had a database table called posts that stored the date a blog post was created in a column called postdate. here’s how I would retrieve the post date in the different formats described above as a field called post_date:
-- Select the column postdate and format it as a long date (e.g. Monday, 12th May, 2016) SELECT DATE_FORMAT(postdate,'%W, %D %M, %Y') as post_date FROM posts
You don’t have to use a column name – if you know the date you want to present, you can pass it as a literal string instead of a column name:
-- Select 2016-06-29 and format it as a short date (29-Jun-16) SELECT DATE_FORMAT('2016-06-29','%d-%b-%y') as post_date FROM posts
Example date styles using DATE_FORMAT
|Date Style||DATE_FORMAT syntax||Output|
DATE_FORMAT(column,'%W, %D %M, %Y')
|Thursday, 31st May, 2016|
MySQL provides an easy way to get dates from your tables in pretty much any format you choose thanks to the DATE_FORMAT function. The full details are available on the MySQL manual page.