Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mysql date

Posted on 2011-10-27
16
Medium Priority
?
415 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:beridius
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 11

Accepted Solution

by:
kelvinwkw earned 1000 total points
ID: 37036440
in mysql
SELECT DATE_FORMAT(now(), '%d/%m/%Y');
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 37036747
You can also do like this,

<?php

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

var_dump($sp_date);

?>
0
 
LVL 10

Assisted Solution

by:ukerandi
ukerandi earned 1000 total points
ID: 37036763
$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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:beridius
ID: 37036798
thanks kelvinwkw the field is clientDate  which in a table called inter
so how would I use your method?
0
 
LVL 11

Expert Comment

by:kelvinwkw
ID: 37036805
SELECT DATE_FORMAT(clientDate, '%d/%m/%Y') from <table_name>;
0
 
LVL 2

Author Comment

by:beridius
ID: 37036903
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
 
LVL 11

Expert Comment

by:kelvinwkw
ID: 37036914
Try this one

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

Author Comment

by:beridius
ID: 37036924
that brings up the date in the right format but it does not update the table to allways to display that way
0
 
LVL 2

Author Comment

by:beridius
ID: 37036943
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
 
LVL 11

Expert Comment

by:kelvinwkw
ID: 37036948


There could be a date format function in the PHP.

For that case, you may try ukerandi's solution
0
 
LVL 11

Expert Comment

by:kelvinwkw
ID: 37036955

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

0
 
LVL 2

Author Comment

by:beridius
ID: 37036969
the php way I would have to change it back that sql would be able to save it??
0
 
LVL 2

Author Comment

by:beridius
ID: 37036990
thank you ukerandi you code works to convert it but on the save it errors saying invaild date format?
0
 
LVL 7

Expert Comment

by:Vimal DM
ID: 37037207
Hi,

See the database accepted format and change accordingly in the server side on MySQL side
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37037746
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
 
LVL 10

Expert Comment

by:ukerandi
ID: 37037755
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question