How to convert a mysql datetime in PHP to m/d/y format?

Question

I am trying to convert a mysql DATETIME into this format m/d/y but the code below is not working, returns 12/31/1969 instead can someone show me how to do this?

$fromMYSQL = '2007-10-17 21:46:59'; //this is the result from mysql DATETIME field
echo date("m/d/Y", $fromMYSQL);

Answer 1

Two issues:

1) You need a capital Y

2) You need a correct date type.

Try

$fromMYSQL = date_create  ('2007-10-17 21:46:59');    
echo date("m/d/Y", $fromMYSQL);

Oops, strtotime is the right conversion to a timestamp. date_create returns a DateTime object.

Answer 2

You need a capital Y int the date format string. the lowercase y gives a two digit year and the upper case 'Y' give a four digit year.

$fromMYSQL = '2007-10-17 21:46:59';
echo date("m/d/Y", strtotime($fromMYSQL));

PHP Manual Page For date may be of some assistance.

Answer 3

I think what you really want is this:

$fromMYSQL = '2007-10-17 21:46:59';
echo date("m/d/Y", strtotime($fromMYSQL));

The second argument of date is a timestamp and I think what is happening is PHP sees your string as a -1 timestamp... thus 12/31/1969.

So, to get a timestamp from the string version of the date, you use strtotime

Answer 4

SQL:

SELECT whatever, UNIX_TIMESTAMP(date) date FROM table WHERE whatever

PHP:

date('m/d/Y', $result['date']);

Answer 5

I covered this in my answer to http://stackoverflow.com/questions/499014/i-need-to-change-the-date-format-using-php/499021#499021 - basically, date() expects a Unix timestamp from which it calculates the date/time and formats that. You're passing in a string which comes from the MySQL query result, which probably gets mangled by PHP's duck typing into something that looks like a Unix timestamp, but makes no sense.

I explain a couple of approaches to dealing with date columns in my answer.

Answer 6

The best way is to convert the dateformat direct in your Querystring:

$TimeFormat = "%m/%d/%Y";  // your pref. Format

$sql = "SELECT DATE_FORMAT(DateCol , '" . $TimeFormat . "') as ConvertDate FROM tblTest";

Otherwise you can modify this function to your needs:

function format_date($original, $format) {
    if (empty($original)) {
     $original = date("Y-m-d H:i:s");
    }
    $original = ereg_replace("30 Dez 1899", "30-01-1973", $original);
    $format = ($format=='date' ? "%m-%d-%Y" : $format);
    $format = ($format=='germandate' ? "%d.%m.%y" : $format);
    $format = ($format=='germandaydate' ? "%A, %d.%m.%Y" : $format);
    $format = ($format=='germantime' ? "%H:%M" : $format);
    $format = ($format=='germandatetime' ? "%d.%m.%y %H:%M:%S" : $format);
    $format = ($format=='datetime' ? "%m-%d-%Y %H:%M:%S" : $format);
    $format = ($format=='mysql-date' ? "%Y-%m-%d" : $format);
    $format = ($format=='mysql-datetime' ? "%Y-%m-%d %H:%M:%S" : $format);
    $format = ($format=='mssql-date' ? "%Y%m%d" : $format);
    $format = ($format=='mssql-datetime' ? "%Y%m%d %H:%M:%S" : $format);
    $format = ($format=='Ymd' ? "%Y-%m-%d" : $format);
    return !empty($original) ? strftime($format, strtotime($original)) : "";
}