Convert a MySQL date field using PHP Functions

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO or MySQLi tutorial.This post will be delete or revised in the future.

Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.

Since you are trying to convert the date I am assuming you can connect to a database, runa query, and display the results. If not check out some of our older tutorials:

How to Access a MySQL Database Using PHP

Creating a Form that will Search a MySQL Database

You can use the function in the query to convert the date or after the results are returned.

Using date_format()

Here is an example of how to call the function in a query:

$query = mysql_query("select * date_format(date, '%b %d') as newdate from `table`")

Then when your results are returned simple echo $row[newdate]. The date I used will output the date in this format: Feb 18th (abbreviated month and numeric day with suffix).
You need to pass the date string and the format mask to the function: date_format($date, $format)

You can pretty much do this the same way after your results are returned.
For example:

	$query = mysql_query("select * from `table` ");	
	while ($row = mysql_fetch_array($query)){
		$newdate = date_format(strtotime($row[date]), '%b %d');

strtotime will convert a string date to a time stamp.

Other formatting option for date_format()

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)

This can also be done using the date function. Something like:

$day = date(“d”,strtotime($row[‘date’]));
$month = date(“M”,strtotime($row[‘date’]));

would give you Mar 03