Need the PHP query to display the date as MM-DD-YYYY instead of YYYY-MM-DD

Question

I need the PHP query to display the date as MM-DD-YYYY instead of YYYY-MM-DD. Here is my code to retrieve the date, just don’t know how to convert the date format.

$sql = 'SELECT * FROM tmp ORDER BY photodate ASC'; 
//display results  
$result = $conn->query($sql);
if ($result->num_rows > 0) {     
    // output data of each row     
    while($row = $result->fetch_assoc()) {         
        echo  "<tr class=table-row><td align=center>".$row["photodate"]."<br><center>"."</center><br></td></tr>";
    }     
    echo "</table>"; 
}
else{
     echo "0 results";
  }
arsort($band);
$conn->close();

?>

Sorry, but that code makes my eyes water. Please invest the time to format your code such that it can be read. There is an `edit` link below your question. _Use it._ — arkascha, Nov 10 '16 at 08:16

score 2 · Answer 1 · answered Nov 10 '16 at 08:16

2

You can do it in mysql in the following way:

SELECT DATE_FORMAT(photodate, '%m-%d-%Y') FROM tmp ORDER BY photodate ASC

answered Nov 10 '16 at 08:16

krasipenkov

2,031
1
11
13

This seems a better approach. – Samay Nov 10 '16 at 08:18
looks good, but i thought PHP is the better idea. @Samay – devpro Nov 10 '16 at 08:20
Why do you say so @devpro? I assume its better off to get formatted date from MySQL rather than formatting it in PHP, although there won't be much of difference when it comes to performance. – Samay Nov 10 '16 at 08:25
@Samay: just because of performance, using function in mysql is a good approach but when really needed, when u can control it by ussing php than no need – devpro Nov 10 '16 at 08:34
I'd have thought that the formatting really depends on the clients' locale. i.e. Someone in the United States is likely to want to see a date in the US format, rather than say the UK format. – Progrock Nov 10 '16 at 09:36

score 1 · Answer 2 · answered Nov 10 '16 at 08:16

1

Use the date_format function. Here is a sample.

$date=date_create($row["photodate"]);
echo date_format($date,"m-d-Y");

answered Nov 10 '16 at 08:16

Samay

465
6
19

score 1 · Answer 3 · answered Nov 10 '16 at 08:18

1

$x = "1993-05-18";
echo date('m-d-Y', strtotime($x))

Result:

05-18-1993

Check this out on how to correctly format date and time on PHP

http://php.net/manual/en/function.date.php

answered Nov 10 '16 at 08:18

Irvin

830
5
13

1

vote for manual link . – devpro Nov 10 '16 at 08:20

score 0 · Answer 4 · answered Nov 10 '16 at 08:20

0

you can try using sql FORMAT() function

check following example

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_format&ss=-1

and change the date format in the query to the one you need

answered Nov 10 '16 at 08:20

Mr Singh

114
9

Need the PHP query to display the date as MM-DD-YYYY instead of YYYY-MM-DD

4 Answers4