Formatting year in PHP from SQL database using date()?

Question

I have upload dates for images stored in a MySQL database as type 'datetime'.

e.g. 2013-04-16 00:20:00

But no matter what I do, I can't seem to get the date to echo correctly in my PHP code. This is what I currently have:

$session_query = mysql_query($sql);
$upload_date_formatted = date('M jS, Y', $session_query['upload_date']);
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

Using the example above, this always gives: 'Uploaded on Jan 1st, 1970.'.

Any ideas? I've tried loads of things, but I just can't seem to get this date to display correctly.

Answer 1

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

---

You need to call mysql_fetch_assoc() on $session_query to convert it to a usable array.

You will also need to pass $session_query['upload_date'] through strtotime() to convert it to the integer that date() is expecting.

$session_query = mysql_query($sql);
$row = mysql_fetch_assoc($session_query);
$upload_date_formatted = date('M jS, Y', strtotime($row['upload_date']));
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

As it is you are attempting to pass an array key from a non-array, which will be null, which when cast to an integer by date() will be zero, resulting in the 01/01/1970 date you are seeing. This will also raise a couple of errors - you should turn on error reporting as it will help you track down errors in the development environment much more quickly:

error_reporting(-1);
ini_set('display_errors', 1);

---

Alternatively you can use the DateTime object, and the createFromFormat() method:

$session_query = mysql_query($sql);
$row = mysql_fetch_assoc($session_query);
$date = DateTime::createFromFormat('Y-m-d H:i:s', $row['upload_date']);
$upload_date_formatted = $date->format('M jS, Y');

Answer 2

You need to convert it to a Unix timestamp first using strtotime(), then you can format it using date().

$session_query = mysql_query($sql);
$upload_date_formatted = date( 'M jS, Y', strtotime( $session_query['upload_date'] ) );
echo '<h6> Uploaded on '.$upload_date_formatted.' .</h6>';

As a side note, the mysql_* extensions have been deprecated. Try PDO instead.

Answer 3

Use the MySQL Date_Format function instead of PHP with the format as '%b %e, %Y'