Skip to main content
Known Participant
April 4, 2010
Answered

Converting timestamp to date

  • April 4, 2010
  • 1 reply
  • 640 views

I'm trying to display the last date a table was updated (the whole table, not just a record in the table) and I've got it working except it's displaying the raw data like this:

2010-04-02 09:45:38

But I just want it to display the date and like this: 04/02/2010 or 4/2/10

Here's the code I've got to display it this far. First the PHP on top of the page:

mysql_select_db($database_connRates, $connRates);
$query_rsTime = "SELECT update_time FROM information_schema.tables WHERE table_schema = 'dpreedge' AND table_name = 'rates'";
$rsTime = mysql_query($query_rsTime, $connRates) or die(mysql_error());
$row_rsTime = mysql_fetch_assoc($rsTime);
$totalRows_rsTime = mysql_num_rows($rsTime);

Then the code in the HTML body itself:

<?php echo $row_rsTime['update_time']; ?>

How do I accomplsih this?

This topic has been closed for replies.
Correct answer Ken Ford

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Try this:

$query_rsTime = "SELECT DATE_FORMAT(update_time, '%m/%d/%Y') AS theDate FROM information_schema.tables WHERE table_schema = 'dpreedge' AND table_name = 'rates'";

And to display it:

<?php echo $row_rsTime['theDate']; ?>

Ken Ford

1 reply

Ken FordCorrect answer
Participating Frequently
April 4, 2010

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Try this:

$query_rsTime = "SELECT DATE_FORMAT(update_time, '%m/%d/%Y') AS theDate FROM information_schema.tables WHERE table_schema = 'dpreedge' AND table_name = 'rates'";

And to display it:

<?php echo $row_rsTime['theDate']; ?>

Ken Ford

ladobeugmAuthor
Known Participant
April 4, 2010

Ken,

That was it! Thanks so much!

Participating Frequently
April 4, 2010

Glad to help!

Ken Ford