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) |
edit:
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
Johnathan Ward is an experienced developer and consultant that writes tutorials to help other developers. In his day job, he is an IBM Watson Consultant with several years of experience deploying and customizing Watson Explorer solutions.