Format Date Fields Using MySQL DATE_FORMAT()
Written by David Walsh on Friday, October 3, 2008
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, for any
“x” not listed
above |
Be kind to your users and format the date for them!
Epic Discussion
Be Heard!
I want to hear what you have to say! Share your comments and questions below.
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?
@julesj: If you’re pulling from a database, you might as well fit them into the query. If not, continue to use date().
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.
Unix timestamps for the win!
For the record, I’ve referenced this page for the fourth time today at work.
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.
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!
@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
Thanks!
I’ve been wondering about what the differences are between timestamp and datetime. are there any big differences between the two?
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.
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.
http://www.mysqlformatdate.com does all the work for you, all you do is choose the format you want.