CODEWITHSUNDEEP
mysqlsqldate
user3365549
user3365549

Reputation: 1

Changing date format from database

I am trying to change the date format before it is displayed with SQL query however the date format is being completely ignored.

my code is 

$query = "SELECT * , DATE_FORMAT(formatted, '%d/%m/%Y') from movies;";

then further down this is my table

echo "<table>"

echo "<table border='2'>"
echo "<tr>
<th>id</th>
<th>title</th>
<th>date</th>
</tr>";

while($row = mysql_fetch_array($query))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['formatted'] . "</td>";

  echo "</tr>";
  }
echo "</table>";
?>

this query is working, however the date format is being ignored and just displaying the date in yyyy-mm-dd I want it in DD-MM-YY.

thanks

Upvotes: 0

Views: 127

Answers (2)

juergen d
juergen d

Reputation: 204746

Use an alias to name your calculated column

SELECT * , DATE_FORMAT(datetime, '%d/%m/%Y') AS formatted_date
from movies

Use a different name than the existing column to differ between the two. Then use 

echo "<td>" . $row['formatted_date'] . "</td>";

to get the formatted one.

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to mention the alias for the formatted datetime column other wise formatted value will not be called in your code

SELECT * ,
DATE_FORMAT(`datetime`, '%d/%m/%Y') `datetime`
from movies

Upvotes: 0

Related Questions