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?

Community · Accepted Answer · 2023-11-17 19:42:58Z

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`][4] 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

I've edited my answer to add more informations ; is your comment-question still valid ? If yes, which possible solution does "this date function" refer to ?
I'm just a bit confused where I insert it! :S Sorry, I'll have have a play and work it out :)
If you choose one of the PHP solutions, you can do that somewhere arround your display, as it's a matter of presentation ;;; if you choose the SQL solution, well, obvisouly, you'll do that in your SQL query (But considering this is presentation, I would prefer having this in the view/template, in PHP)
Ok, what would you say is the best solution to use if I am planning on sorting the table rows by date (afterwards), and also running a query that displays items with dates that have passed a deadline, from what the system date is (if that makes sense)... e.g. system date is set as 20th march 2010, a deadline for activity is set as 21st march...i plan on changing the colour of the table row to red (just an example) to shows its passed date! Will using PHP or MySQL to format the date make any difference to my requirment?

John Conde · Accepted Answer · 2010-03-19 19:56:31Z

4

Just use the Mysql built in function DATE_FORMAT()

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

answered Mar 19, 2010 at 19:56

John Conde

220k100 gold badges464 silver badges504 bronze badges

Comments

D.Martin · Accepted Answer · 2010-03-19 20:06:50Z

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

St. John Johnson · Accepted Answer · 2010-03-19 19:57:42Z

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.

NaN · Accepted Answer · 2013-03-01 12:17:34Z

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.

Chait · Accepted Answer · 2016-04-05 01:57:20Z

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.

Nandhini · Accepted Answer · 2018-03-21 14:24:20Z

This mysql function will return the correct format in the result

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

Akash Dubey · Accepted Answer · 2019-01-03 07:29:16Z

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

Set Date as per your need

Tiza · Accepted Answer · 2021-12-28 05:21:04Z

<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

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

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

9 Answers 9

8 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

8 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Related