Trimming or converting a mysql date field with php

Question

Hello,

I am displaying the contents of a datetime field in mysql in a table with php, and want to show just the date. Is there a way to convert or do this, or must the actual string be editied or trimmed?

Answer 1

Using strtotime...

A simple way is like this:

$date="2009-01-27 14:30:22";
echo strftime("%d %m %Y", strtotime($date));

i.e. turn your mysql date into a time value with strtotime, then reformat it with strftime.

However this only works for dates which can be represented by a unix timestamp - so no dates before 1st Jan 1970 or after 2038

Using PEAR's Date class...

The PEAR Date classes for a more thorough approach which should cope better with historical dates:

$date=new void Date("2009-01-27 14:30:22");
echo $date->format("%d %m %Y");

...or use your DB

Alternatively, have your db format it for you with DATE_FORMAT

SELECT col1,col2,DATE_FORMAT(col2, '%W %M %Y') as col2readable FROM mytable;

Answer 2

You could do it with PHP, but if you don't need the additional baggage, you could consider doing it in your query and return only what you need. This example uses DATE_FORMAT():

"SELECT col1, col2, DATE_FORMAT(dateCol, '%d-%m-%Y') as 'newDate' 
 FROM tablename"