Datetime format PHP and MySQL

Question

I have the following problem. I have a string, which contains time and date as follows: dd/MM/yy hh:mm:ss This I have in PHP. And then I have a MySQL database, which has a column of a datetime format, where I need to insert this value. Obviously the problem is, that the format is different, so instead of the actual date, it results in a field "0000-00-00 00:00:00".

Could you please help me with converting this string and then inserting it properly into MySQL? For the MySQL I use the standard INSERT INTO command.

Answer 1

From the DATETIME documentation:

MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.

I'd use PHP's DateTime class and DateTime::createFromFormat() method, and convert the data into a MySQL-compatible date string, like so:

$date = DateTime::createFromFormat('d/m/Y H:i:s', $yourDateString);
$dateToBeInserted = $date->format('Y-m-d H:i:s');

Answer 2

Write a function to convert date,

function sqldate($date)
{
   $sql_date = date('Y-m-d H:i:s',strtotime($date));
   return $sql_date;
}

And your query look like,

$query = "INSERT INTO tableName (dateColumn) VALUES('".sqldate($date)."') ";

Answer 3

You can convert it using STR_TO_DATE() along with your INSERT statement, eg.

INSERT INTO tableName (dateColumn)
VALUES(STR_TO_DATE('dd/MM/yy hh:mm:ss','%d/%m/%y %H:%i:%s'))

• SQLFiddle Demo

Answer 4

Try this:

$parsedDate = date('Y-m-d H:i:s', strtotime($yourDate));
// Insert $parsedDate into your table column Date