Select Page

Metal storage cubes
[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:
YYYY-MM-DD
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

Date StyleOutput
MySQL2016-05-12
US Short05/12/2016
UK Short12/05/2016
Short12-May-16
LongThursday, 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.

All you have to do is use the MySQL built-in function DATE_FORMAT.

DATE_FORMAT(date,format)

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 StyleDATE_FORMAT syntaxOutput
MySQL
DATE_FORMAT(column,'%Y-%m-%d')
2016-05-31
US Short
DATE_FORMAT(column,'%m/%d/%Y')
05/31/2016
UK Short
DATE_FORMAT(column,'%d/%m/%Y')
31/05/2016
Short
DATE_FORMAT(column,'%d-%b-%y')
31-May-16
Long
DATE_FORMAT(column,'%W, %D %M, %Y')
Thursday, 31st May, 2016

In conclusion

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.