I have a forum in PHP which takes a date like in the form
dd/mm/yyyy hh:mm:ss. However, I need to insert it for SQL as a DATETIME in the format as yyyy-mm-dd hh:mm:ss. How can I convert this data?
-
2This has been asked here a million times. A quick search will find your answer.John Conde– John Conde2012-05-21 15:46:12 +00:00Commented May 21, 2012 at 15:46
Add a comment
|
4 Answers
Your date time format is wrong: dd/mm/yyyy hh:mm:ss. Probably you mean d/m/Y H:i:s
If you have 5.3+ version there is safe way to convert the date time into another format. Here's an example:
$timestamp = '31/05/2001 12:22:56';
$timestamp = DateTime::createFromFormat('d/m/Y H:i:s', $timestamp);
echo $timestamp->format('Y-m-d H:i:s');
or if you like more procedural way:
$timestamp = '31/05/2001 12:22:56';
$timestamp = date_create_from_format('d/m/Y H:i:s', $timestamp);
echo date_format($timestamp, 'Y-m-d H:i:s');
Be careful with previous suggestions. Some are completely wrong and others could lead to errors.
1 Comment
kodeart
To clarify where you can get a problem. When using strtotime() separator is important; in your case it's slash (/) meaning: US format is ASSUMED (m/d/Y). If separator is dash (-) then european format (d-m-Y) is ASSUMED. So, do not assume anything ;)
You can use the strtotime and date to rework the format.
$new_date = date( "Y-m-d H:i:s", strtotime( $old_date ) );
What this does is take your old date (dd/mm/yyyy hh:mm:ss), converts it to a unix timestamp that can then be used with the php date function to format the date to the desired format.
7 Comments
에이바바
I can't get this to work it outputs: string(33) "12121212-0505-0909 1212:0505:0000" From $startDatec= date("yyyy-mm-dd hh:mm:ss", strtotime($_POST['startEventDate']));
jjaybrown98
Sorry the actual format is
Y-m-d H:i:s my format was to illustrate what it would return. My Bad. Take a look here on formatting within date function link So it would be: $finalc= date("Y-m-d H:i:s", strtotime($_POST['finalDate']));kodeart
Your suggestion can lead to wrong conversion. Look what I wrote in the comment.
jjaybrown98
You are correct, the assumption is that common sense will prevail. Your implementation using php 5.3+ would work perfectly but my solution would work for any version, with the correct considerations in mind.
kodeart
I agree to certain extent. Version 5.3+ will replace 5.2+ sooner or later, as in all previous versions. So maybe it's better to think a bit ahead, don't you think?
|
Two of several possible ways:
- Convert in code and then pass the converted value to mysql:
$mysqldate = date( 'Y-m-d H:i:s', $phpdate ); - Let mysql do the work by using its built-in functions:
$query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) ...";
Comments
if you have datetime avaialable from a from like above format then u just need to use following function.
function localToMysql($dateTime){
$date_chunks = explode('/', $dateTime);
$time_chunks = explode(' ', $date_chunks[2]);
$final_format = $time_chunks[0] . "-" . $date_chunks[1] . "-" . $date_chunks[0] . " " . $time_chunks[1];
return $final_format; }
