mysql date

I have a mysql database the date is in the wrong format its in yyyy/mm/dd I need to be in DD/MM/YYYY,

the mysql frontend is in php.
LVL 2
beridiusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kelvinwkwCommented:
in mysql
SELECT DATE_FORMAT(now(), '%d/%m/%Y');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Loganathan NatarajanLAMP DeveloperCommented:
You can also do like this,

<?php

$sp_date = preg_split('/[- :]/', $date);

var_dump($sp_date);

?>
0
ukerandiCommented:
$originalDate = "2010-03-21";  
$newDate = date("d-m-Y", strtotime($originalDate));

strtotime Function
The function expects to be given a string containing an English date format
and will try to parse that format into a Unix timestamp
(the number of seconds since January 1 1970 00:00:00 UTC),
relative to the timestamp given in now, or the current time if now is not supplied.

This function will use the TZ environment variable (if available) to calculate the timestamp.
Since PHP 5.1.0 there are easier ways to define the timezone that is used across all date/time functions.
That process is explained in the date_default_timezone_get() function page.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

beridiusAuthor Commented:
thanks kelvinwkw the field is clientDate  which in a table called inter
so how would I use your method?
0
kelvinwkwCommented:
SELECT DATE_FORMAT(clientDate, '%d/%m/%Y') from <table_name>;
0
beridiusAuthor Commented:
Kelvin all that does is show me the date it does not update the table to display of am I doing something wrong

SELECT DATE_FORMAT(clientDate, '%d/%m/%Y') from inter.tblclients;

Open in new window

0
kelvinwkwCommented:
Try this one

SELECT DATE_FORMAT(inter.tblclients.clientDate, '%d/%m/%Y') from inter.tblclients;
0
beridiusAuthor Commented:
that brings up the date in the right format but it does not update the table to allways to display that way
0
beridiusAuthor Commented:
could I do something like
UPDATE `inter`.`tbl_client` SET `clientDate` = DATE_FORMAT(clientDate, '%d/%m/%Y') from inter.tbl_client`clientDate` WHERE `tbl_client`.`clientID` =6;

Open in new window

0
kelvinwkwCommented:


There could be a date format function in the PHP.

For that case, you may try ukerandi's solution
0
kelvinwkwCommented:

You may try but the result should be the same if the field is set to datetime or date.

0
beridiusAuthor Commented:
the php way I would have to change it back that sql would be able to save it??
0
beridiusAuthor Commented:
thank you ukerandi you code works to convert it but on the save it errors saying invaild date format?
0
Vimal DMSenior Software EngineerCommented:
Hi,

See the database accepted format and change accordingly in the server side on MySQL side
0
ukerandiCommented:
if need to UK date format setting u have to change computer date format, then it will change the Mysql date format,i didn't check but you have to worth to check
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.