We help IT Professionals succeed at work.

mysql date format

beridius
beridius asked
on
is they away to convert the date format from yyyy-mm-dd to dd-mm-yyyy
Comment
Watch Question

Commented:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

Commented:
If you mean to change your database and revise all the values in it, I strongly recommend against that.  There are a lot of reasons to keep the standard MySQL format.

If you mean to change the format as you select a date, try this:

SELECT DATE_FORMAT(YourDateField, "%d-%m-%Y) FROM Yourtable WHERE .....

Commented:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%d %m %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%d %m %Y') |
+------------------------------------------------+
| 04 10 2009                                     | 
+------------------------------------------------+
1 row in set (0.00 sec)

Open in new window

Commented:
Forgot a quote:

SELECT DATE_FORMAT(YourDateField, "%d-%m-%Y") FROM Yourtable WHERE .....

Commented:
mysql> SELECT DATE_FORMAT('2009-10-04', '%d-%m-%Y');
+---------------------------------------+
| DATE_FORMAT('2009-10-04', '%d-%m-%Y') |
+---------------------------------------+
| 04-10-2009                            | 
+---------------------------------------+
1 row in set (0.00 sec)

Open in new window

Author

Commented:
when I update the date from a variable  would I use  
UPDATE DATE_FORMAT(YourDateField, "%d-%m-%Y") FROM Yourtable WHERE

Open in new window

CERTIFIED EXPERT
Commented:
>>when I update the date from a variable  would I use  <<

Doing so totally unnecessary, because MySQL will store the date in it's own way internally. Normally you will format when you display (with a SELECT query or at the application layer) not when storing the data.

Now if you still want to store a formatted date, you will have to do so in a varchar column, but it's not a good idea neither because it will cause you lots of trouble later if you need to do date calculations
Commented:
Your DateField should be the standard MySQL format -  yyyy-mm-dd

If you have the date in your program in a different format, then convert it to the MySQL format before entering it into the database:

$date = "31-02-2011";
$day = substr($date,0,2);
$month = substr($date,3,2);
$year = substr($date,6,4);
$mysqldate = $year."-".$month."-".$day;

then use $mysqldate to update your table.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.