MySQL date_format: To get date in different styles.

Author: Zeeshan Rasool | Apr 13, 2010

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:

SELECT title, DATE_FORMAT(date, '%d-%m-%Y') 
    -> FROM users;

1 2	SELECT title, DATE_FORMAT(date, '%d-%m-%Y') -> FROM users;

Recommended WordPress Themes Stores

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 99points.info@gmail.com

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