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

MySQL stores the date in my database (by default) as 'YYYY-MM-DD' The field type for my date is 'DATE' (I do not ne开发者_运维知识库ed 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?

---

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

---

Just use the Mysql built in function DATE_FORMAT()

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

---

You can display you date in any format you want in your pages, in mysql i realy don't know, in php you can use this function: date ( string $format [, int $timestamp ] ). So you can do this:

echo date( "d/m/Y", strtotime ( $your_date ) );

You can find full description here: http://php.net/manual/en/function.date.php

---

After getting it from the DB, use $time = strtotime($value) to convert it to a timestamp in PHP. Then use date("d/m/Y", $time) to get it in that format.

---

If you used some Framework like Zend, it would be very easy because you have plenty of classes to handle databases and date formatting. Then you would always see the output as the Brazilian date format. Just like Delphi TDateField uses Windows date format in the current computer, Zend will look up in your own configuration.

---

i'm using this script and put it on upper line.

$(function(){
    var pickerOpts = {
        dateFormat: "yy-mm-dd"
    };  
    $("#datepicker").datepicker(pickerOpts);
});

and this one in your form.

<input id=**"datepicker" type="date"** name="Start"  size="9" value="<?php  

echo $Start;

?>" />

It will appear as d/m/y on your page but in your database y/m/d.

---

This mysql function will return the correct format in the result

SELECT DATE_FORMAT(date_field, "%M %d %Y");

---

$dtShowDate = $_POST['dtShowDate']; $date =explode('-', $dtShowDate)0; $showdate = $date[2]."-".$date[0]."-".$date[1];

Set Date as per your need

---

<table class="table-responsive-sm text-center" id="">
    <tr>
        <th>NO.</th>
        <th>DATE REQUIRED</th>
        <th>DATE APPLY</th>
    </tr>
    <?php foreach ($data as $s) : ?>
        <tr>
            <td><?php echo $no++ ?></td>
            <td><?php $originalDate = $s['Daterequired'];  ?> 
            <?= $newDate = date("d-m-Y", strtotime($originalDate)); ?></td>
            <td><?php $originalDate2 = $s['Dateapply'];  ?>
            <?= $newDate2 = date("d-m-Y", strtotime($originalDate2)); ?></td> 
        </tr>
    <?php endforeach ?>
</table>

//$data is sql command from database
//$s[""] is virables name in database
//Hopefully this is useful for the question

The Result