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

by

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 :

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

More Related Contents:

  1. How to convert ISO8601 to Date format in php
  2. Order results from two queries based on time in second query [closed]
  3. Query database then email posts from where user is subbed to
  4. Php date() Date and time is stuck [closed]
  5. Save the data into a database and return another value from database using PHP
  6. How can I do ‘insert if not exists’ in MySQL?
  7. Can I parameterize the table name in a prepared statement?
  8. Getting raw SQL query string from PDO prepared statements
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. MySQL Prepared statements with a variable size variable list
  11. PHP mysql insert date format
  12. PDO with INSERT INTO through prepared statements [closed]
  13. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  14. Comma separated values in MySQL “IN” clause
  15. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  16. Doing a while / loop to get 10 random results
  17. Set timezone in PHP and MySQL
  18. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  19. MYSQL Select from tables based on multiple rows
  20. Simple PHP SQL login troubleshooting
  21. How do you do multiple SQL statements in one mysql_query?
  22. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  23. Laravel Eloquent get results grouped by days
  24. MySQL/SQL retrieve first 40 characters of a text field?
  25. how do I get month from date in mysql
  26. Do I have to use mysql_real_escape_string if I bind parameters?
  27. MySQL, how to insert null dates
  28. PDO and MySQL ‘between’
  29. MySQL Join Multiple Rows as Columns
  30. Aggregated query without GROUP BY

Leave a Comment