15

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?

Improve this question

9 Answers 9

Reset to default
26

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 :

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Where do I use this date function and how?
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?
|
4

Just use the Mysql built in function DATE_FORMAT()

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

Comments

2

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

Improve this answer

Comments

1

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

This mysql function will return the correct format in the result

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

Comments

0

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

Set Date as per your need

Improve this answer

Comments

0 

<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

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.