O'Reilly

Format Date Fields Using MySQL DATE_FORMAT()

By on  

I use MySQL DATE and DATETIME fields almost as much as VARCHAR fields. I use them mostly to track record creation, record updates, and record expiration dates. The default date format in MYSQL is:

2008-09-10 22:50:02

When I pull this information into the page, I want to show the date in human-readable format. That's where MySQL's DATE_FORMAT functionality comes in. DATE_FORMAT allows me to format the date in any manner you'd like.

MySQL DATE_FORMAT() Example

DATE_FORMAT(NOW(),'%W, %M %e, %Y @ %h:%i %p')
#yields 'Sunday, September 20, 2008 @ 12:45 PM'

MySQL DATE_FORMAT() Letter Representations

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above

Be kind to your users and format the date for them!

Track.js Error Reporting

Upcoming Events

Recent Features

  • 7 Essential JavaScript Functions

    I remember the early days of JavaScript where you needed a simple function for just about everything because the browser vendors implemented features differently, and not just edge features, basic features, like addEventListener and attachEvent.  Times have changed but there are still a few functions each developer should...

  • Vibration API

    Many of the new APIs provided to us by browser vendors are more targeted toward the mobile user than the desktop user.  One of those simple APIs the Vibration API.  The Vibration API allows developers to direct the device, using JavaScript, to vibrate in...

Incredible Demos

  • 9 More Mind-Blowing WebGL Demos

    With Firefox OS, asm.js, and the push for browser performance improvements, canvas and WebGL technologies are opening a world of possibilities.  I featured 9 Mind-Blowing Canvas Demos and then took it up a level with 9 Mind-Blowing WebGL Demos, but I want to outdo...

  • Create a 2 Column Layout with Flexbox

    Flexbox was supposed to be the pot of gold at the long, long rainbow of insufficient CSS layout techniques.  And the only disappointment I've experienced with flexbox is that browser vendors took so long to implement it.  I can't also claim to have pushed flexbox's limits, but...

Discussion

  1. I like to do this kind of transformations on the PHP side, with date(), mktime() and the like. Does anybody know which method is quickest?

  2. @julesj: If you’re pulling from a database, you might as well fit them into the query. If not, continue to use date().

  3. Jeff Hartman

    I typically store all dates in an integer field. That was a habit I got into when most hosts and clients were on MySQL 4.x and there was no datediff function (from what I recall).

    A drawback is that it is impossible to read as an integer when it is stored in the database. However, I find it simple to use with PHP functions. No worries about whether or not the query is formatted properly, etc.

  4. Unix timestamps for the win!

  5. For the record, I’ve referenced this page for the fourth time today at work.

  6. I always use integers to store dates as unixtime. I find that this way it’s easier to do arithmetic on the values before formatting using PHP’s date() function.

  7. Juan Gotti

    This is a great post, Thanks.

    This programming world has been really hard for me because I first needed to lear the language (English) and the apply it to programming, hehe tough task.

    My question is what if I want to have the date in Spanish, what’s the most practical way to crear a custom date function to use it in other languages?

    Thanks in advance!

  8. @Juan: I don’t think MySQL is the best match for that. I would try writing a PHP function to do it. Here’s a good starting point:

    http://php.net/date

  9. Juan Gotti

    Thanks!

  10. richard

    I’ve been wondering about what the differences are between timestamp and datetime. are there any big differences between the two?

  11. Bob

    SELECT DATE_FORMAT(time, ‘%Y%c%e’) …..

    Can anyone tell me why this snippet of code errors everytime due to the percentage signs. It does, everytime, yet every example that is ever illustrated uses this convention.

    What gives? If I remove the percentages I get the correct results in other scenarios, just not the correct format.

    Thanky.

  12. Hi, I am trying to compare a group of dates with current date and find if its previous so that i could mark it as expired. I tried with strtotime() but it can compare only the current years date and converts the date of other year’s to current years.
    I hope to hear from someone else…Thank you.

  13. http://www.mysqlformatdate.com does all the work for you, all you do is choose the format you want.

  14. @Juan Gotti: hey hello juan this could be prob little late just wanted to give you an option to do this

     $sql = query ("SELECT DATE_FORMAT(date, '%a %d %b %Y %h::%i:%S %p') AS date FROM usuarios WHERE id = '$login'");
     $row = fetch ($sql);
     $eng = array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Mon','Tue','Wed','Thu','Fri','Sat','Sun');
     $esp = array('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic','Lunes','Martes','Miercoles','Jueves','Viernes','Sábado','Domingo');
     $row['date'] = str_replace($eng,$esp,$row['date']);
    
  15. Another gem from the meister – I should have a favorites subfolder called “Dave’s invaluable snippets”

    Cheers again Dave, keep em coming!

  16. u have given good information. but pls tell us code as to how date should be inserted in database, particularly if users input date in dd-mm-yyyy format.

  17. pls give clarification about the difference between date, time and timestamp datatypes in mysql.

  18. aneesh

    Hi
    thanks for explaining this

  19. byh

    Thanks for the great post. Im using this to fetch data from a website.

  20. Thanks, David. This is a great resource! Say, any idea how one might format AND order by date? I’m getting very unexpected results.

  21. Kyaw Lwin Phyo

    Hi, I’ve tried
    “SELECT ‘Date:’, NOW() into OUTFILE ‘E:\\SUBCON\\EDWHFiles\\Threshold\\time.txt'”
    but I’m getting this value “Date: Wed\ 30\ Nov\ 2011\ 04:59:36\ PM”.
    why is this happening????
    Can anyone explain and correct me??
    Thanks.

  22. Kyaw Lwin Phyo

    SELECT ‘Date:’, DATE_FORMAT(NOW(),’%a %d %b %Y %r’) into OUTFILE ‘E:\\SUBCON\\EDWHFiles\\Threshold\\time.txt’

    sorry the query is like this, not as the above.

  23. Aneeq

    I have found a good solution on the site below. Works like charm for me.

    http://phphelp.co/2012/03/28/how-to-print-all-the-months-and-years-between-two-dates-in-php/

    Or

    http://addr.pk/ac8b

  24. wizard

    Awesome Post, Extremely Thankful to you for your invaluable time and effort.

  25. pratik

    thanks it saved time

  26. Shalini

    Wonderful example,i t was same what i was searching for.

    Thanks a lot :)

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!

Recently on David Walsh Blog

  • OâReilly Velocity Conference â New York

    My favorite front-end conference has always been O'Reilly's Velocity Conference because the conference series has focused on one of the most undervalued parts of client side coding:  speed.  So often we're so excited that our JavaScript works that we forget that speed, efficiency, and performance are just as important. The next Velocity...

  • Free Download: Font Bundle Featuring 17 Incredible Typefaces

    The only thing we love more than a good font, is a good free font. So we’ve combed the Web for some of our favorite free fonts, and gathered them here in a single download. You’ll find a variety of useful typefaces, from highly geometric designs...

  • OâReilly Velocity Conference â Amsterdam

    My favorite front-end conference has always been O'Reilly's Velocity Conference because the conference series has focused on one of the most undervalued parts of client side coding:  speed.  So often we're so excited that our JavaScript works that we forget that speed, efficiency, and performance are just as important. The next Velocity...

  • CanIUse Command Line

    Every front-end developer should be well acquainted with CanIUse, the website that lets you view browser support for browser features.  When people criticize my blog posts for not detailing browser support for features within the post, I tell them to check CanIUse:  always up to date, unlike...

  • Generating Alternative Stylesheets for Browsers Without @media

    If your CSS code is built with a mobile-first approach, it probably contains all the rules that make up the "desktop" view inside @media statements. That's great, but browsers that don't support media queries (IE 8 and below) will simply ignore them, ending up getting the...