Changing date format from database

Question

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

As a side note, you really shouldn't be doing the formatting in SQL - leave that to your application layer (or even the client side display layer - have JavaScript do it). Formats are culture/locale specific, and your db almost certainly doesn't have the relevant information about the client to know how to display it. For instance, Europeans tend to use MM/DD/YYYY, and if you don't spell it out, they're going to be thinking of the wrong month/day... — Clockwork-Muse
– Clockwork-Muse, Commented Mar 9, 2014 at 14:16
hmm. Valid point! Ill try working towards that instead! appreciate the input! — user3365549
– user3365549, Commented Mar 9, 2014 at 14:38

juergen d · Accepted Answer · 2014-03-08 20:22:18Z

2

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.

answered Mar 8, 2014 at 20:22

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3365549 Over a year ago

You sorted this issue in 2 minutes, iv been fighting for around hours!! Appreciate it keep it up!

M Khalid Junaid · Accepted Answer · 2014-03-08 20:21:09Z

0

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

answered Mar 8, 2014 at 20:21

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

4 Comments

user3365549 Over a year ago

I did not down vote it, however i think you might have been down voted due to the ` instead of ' but I understood it. Thanks for the input though.

M Khalid Junaid Over a year ago

@user3365549 no bacticks is the standard practice to use in mysql arround db/table/column name

user3365549 Over a year ago

hmm yes with phpmyAdmin, but if you try with html it fails.

M Khalid Junaid Over a year ago

@user3365549 in php you can access it as <?php echo $row['datetime']?> without bacticks and never call with backticks in php its a mysql escape

Collectives™ on Stack Overflow

Changing date format from database

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related