Change date format (in DB or output) to dd/mm/yyyy - PHP MySQL

Question

MySQL stores the date in my database (by default) as 'YYYY-MM-DD' The field type for my date is 'DATE' (I do not need any time storage).. Is there a simple way to change it by default to DD/MM/YYYY ?

I call up different dates in 2 different tables, and no where in any of my code do I have anything resembling a date variable or anything! Hopefully this is a straight forward change?

Answer 1

In PHP, you could :

• Transform the date to a timestamp, using strtotime
• Format it, using date

A bit like this, I'd say :

$timestamp = strtotime($date_from_db);
echo date('d/m/Y', $timestamp);

But this will only work for dates between 1970 and 2038, as timestamps are stored as 32 bits integers, counting from 1970-01-01.

In MySQL, I suppose the date_format function would do the trick.
For example :

mysql> select date_format(curdate(), '%d/%m/%Y');
+------------------------------------+
| date_format(curdate(), '%d/%m/%Y') |
+------------------------------------+
| 19/03/2010                         |
+------------------------------------+
1 row in set (0.03 sec)

And, for the sake of completness, another solution, in PHP, that doesn't suffer from the limitation of 1970-2038 would be to use the DateTime class, and, especially :

• DateTime::__construct to parse the date returned by the DB
• DateTime::format to format the date to whatever format you want.

For example, this portion of code :

$date = new DateTime('2010-03-19');
echo $date->format('d/m/Y');

would get you this output :

19/03/2010

Answer 2

Just use the Mysql built in function DATE_FORMAT()

SELECT DATE_FORMAT(some_date_field, "Y/m/d");