7

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?

에이바바
  • 1,011
  • 11
  • 36
  • 60

4 Answers4

19

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.

kodeart
  • 1,903
  • 1
  • 19
  • 27
  • 3
    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 ;) – kodeart May 21 '12 at 16:07
4

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.

에이바바
  • 1,011
  • 11
  • 36
  • 60
jjaybrown98
  • 89
  • 6
  • 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'])); – 에이바바 May 21 '12 at 15:54
  • 1
    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](http://php.net/manual/en/function.date.php) So it would be: `$finalc= date("Y-m-d H:i:s", strtotime($_POST['finalDate']));` – jjaybrown98 May 21 '12 at 16:04
  • 2
    Your suggestion can lead to wrong conversion. Look what I wrote in the comment. – kodeart May 21 '12 at 16:09
  • 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. – jjaybrown98 May 21 '12 at 16:14
  • 1
    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? – kodeart May 21 '12 at 16:18
  • Possibly, however there wasn't that scope in the question to presume, so it's best to go for the one-size-fits-all – jjaybrown98 May 21 '12 at 16:43
  • This answer is more general than the accepted answer and what I was looking for as most dates you're converting aren't going to have the specific format the accepted answer has. –  Aug 19 '19 at 05:34
2

Two of several possible ways:

  1. Convert in code and then pass the converted value to mysql: $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
  2. Let mysql do the work by using its built-in functions: $query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) ...";
Jeshurun
  • 22,940
  • 6
  • 79
  • 92
1

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; }

jugnu
  • 141
  • 6