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:
1.$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:
1.$query = mysql_query("select * from `table` ");
2.while ($row = mysql_fetch_array($query)){
3. $newdate = date_format(strtotime($row[date]), '%b %d');
4.}
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
