Changing Date Format In MySQL

[tomyknoker]

How can I change the way MySQL displasy the date format on my php page? At the moment it displays like YYYY/MM/DD and I want it to display DD/MM/YYYY... Any ideas?

[codeexploiter]

You can change your SQL query through which you are retrieving the date values from your table, which is in MySQL date format (YYYY-MM-DD).

An exampl query is as follows:

SELECT date_format( datetable.testdate, '%d/%m/%Y' )FROM datetable;

In the above statement we are using date_format function of MySQL to format the date field of your table so it will return the formatted date to your PHP script and you can display it.

The item in the red is the tablename.fieldname and the item specified in the blue color is the format you want %d - day, %m - month and %Y - Year.

Please let me know if you have any problem with the explanation

[tomyknoker]

Hi there I'm only just learning PHP, so My table looks something like this... Can I Still keep my table? Or oes it all need to change

?>
  <tr valign="top">
    <td><?php echo $qry['ID']; ?></td>
	<td><?php echo $qry['FirstName']; ?></td>
    <td><?php echo $qry['LastName']; ?></td>
    <td><?php echo $qry['State']; ?></td>
	<td><?php echo $qry['JoinDate']; ?></td>
    <td><?php echo $qry['loginDateTime']; ?></td>
  </tr>
<?php

[codeexploiter]

Can you post the SQL query you are using to retrieve the data from your table?

[tomyknoker]

No probs... Ok here is the code from that page

//approved
$info = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='$cat'");

echo '<table border="1" cellpadding="3" cellspacing="1">
  <tr valign="top">
    <td>ID</td>
	<td>First Name</td>
    <td>Last Name</td>
    <td>State</td>
	<td>Application Date</td>
    <td>Last Login</td>
  </tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
  <td colspan="6">There are no members that match the query. Please go back and try again</td>
    </tr>';
}

else {
   while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
  <tr valign="top">
    <td><?php echo $qry['ID']; ?></td>
	<td><?php echo $qry['FirstName']; ?></td>
    <td><?php echo $qry['LastName']; ?></td>
    <td><?php echo $qry['State']; ?></td>
	<td><?php echo $qry['JoinDate']; ?></td>
    <td><?php echo $qry['loginDateTime']; ?></td>
  </tr>
<?php

[codeexploiter]

"SELECT ID,FirstName,LastName,date_format(JoinDate,'%d/%m/%Y') FROM `tblmembers` WHERE `MemberApproved`='$cat'"

Try the above SQL query instead of yours and see.

[tomyknoker]

What do you think about this? Is seems to work but if it's a dodgy way then I'll go with yours

<tr valign="top">
<td><?php echo $qry['ID']; ?></td>
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['State']; ?></td>
<td><?php echo date('d/m/Y', strtotime($qry['JoinDate'])); ?></td>
<td><?php echo date('d/m/Y', strtotime($qry['loginDateTime'])); ?></td>
</tr>