MySQL date_format: To get date in different styles.

Author: Zeeshan Rasool  |  Last updated on MySQL, PHP

My SQL has a special function that allows the developer to format the date in any way they wish by specifying a sequence of format strings. A string is composed of the percentage symbol ‘%‘ followed by a letter that signifies how you wish to display part of the date. These are some of the more common strings to use:

String Displays Example
%d The numeric day of the month 01….10….17….24 etc
%D The day of the month with a suffix 1st, 2nd, 3rd…. etc
%m The numeric month 01….04….08….11 etc
%M The Month name January….April….August etc
%b The Abbreviated Month Name Jan….Apr….Aug….Nov etc
%y Two digit year 98, 99, 00, 01, 02, 03 etc
%Y Four digit year 1998, 2000, 2002, 2003 etc
%W Weekday name Monday…. Wednesday….Friday etc
%a Abbreviated Weekday name Mon….Wed….Fri etc
%H Hour (24 hour clock) 07….11….16….23 etc
%h Hour (12 hour clock) 07….11….04….11 etc
%p AM or PM AM….PM
%i Minutes 01….16….36….49 etc
%s Seconds 01….16….36….49 etc

You can use it in your mysql queries like this:

Recommended WordPress Themes Store

If you enjoyed this post, please consider leaving a comment below or subscribing to the RSS feed to have future articles delivered to your feed reader. You can also follow us on Facebook or Twitter @99Points

This awesome post has been created by 99Points editor's team. You can follow our updates on Twitter and facebook, OR drop an email at

Email Subscriber!

Be the first to know about new updates

Author of 99Points
Zeeshan Rasool is an experienced PHP Web Developer and founder at 99Points. Expert in Codeigniter (MVC), WordPress, JSON, jQuery & HTML5
 LinkedIn Profile




Popular post