Change Date Format (In Db or Output) to Dd/Mm/Yyyy - PHP MySQL

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

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

String to Date conversion for format DD/MM/YYYY in PHP and MYSQL is not working

That's probably because MySQL accepts YYYY-MM-DD format. Try to insert like this,

<?php
     $mydate="18/06/2016";
     $date = DateTime::createFromFormat('d/m/Y', $mydate);
     $dateFormat=$date->format('Y-m-d');
     $sql="INSERT INTO mytable (dob) VALUES ('".$dateFormat."')";

How to convert date from yyyy-mm-dd to dd-mm-yyyy in php?

use this function, which uses inbuilt function 'substr(str,start,length)'.

function getFormatDate($date){
    $date = substr($date,8,2).'-'.substr($date,5,2).'-'.substr($date,0,4);
    return $date;
}

getFormatDate($data2['emp_dob']);

MySQL date format DD/MM/YYYY select query?

You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:

ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')

However, you would be wise to convert the column to the DATE data type instead of using strings.

mysql date format dd/mm/yyyy?

Convert the output from calendar into date format like this

$cal_date=17-08-2012;
$date=date('Y-m-d',strtotime($cal_date));
echo $date; //gives date as 2012-08-17

Now you can insert this date into mysql and do comparisons

Converting Database date format to yyyy-mm-dd and back to dd-mm-yyyy in PHP

createFromFormat is documented to return false on failure, which is exactly what happens here. The program blows up when you try to call format on false, which doesn't make any sense.

The reason for this is that your format string is wrong. For the given input, the correct format string is "d-m-Y" (format strings are also documented on the same page).

Of course this means that your output format string is wrong as well: it should be "Y-m-d".

---

Related Topics