Converting dates in PHP for DATETIME in SQL

Question

I have a forum in PHP that accepts a date, like in the form dd/mm/yyyy hh:mm:ss . However, I need to insert it for SQL as DATETIME in the format yyyy-mm-dd hh:mm:ss . How can I convert this data?

Answer 1

Date format is not valid: dd/mm/yyyy hh:mm:ss . You probably mean d/m/YH:i:s

If you have version 5.3+, there is a safe way to convert the date to another format. Here is an example:

 $timestamp = '31/05/2001 12:22:56'; $timestamp = DateTime::createFromFormat('d/m/YH:i:s', $timestamp); echo $timestamp->format('Ymd H:i:s'); 

or if you like a more procedural way:

 $timestamp = '31/05/2001 12:22:56'; $timestamp = date_create_from_format('d/m/YH:i:s', $timestamp); echo date_format($timestamp, 'Ymd H:i:s'); 

Be careful with previous suggestions. Some of them are completely wrong, while others can lead to errors.

Answer 2

You can use strtotime and date to rework the format.

 $new_date = date( "Ymd H:i:s", strtotime( $old_date ) ); 

This means that your old date ( dd/mm/yyyy hh:mm:ss ) will convert it to a unix timestamp, which can then be used with the php date function to format the date to the desired format.

Answer 3

Two of several possible ways:

• Convert to code, then pass the converted value to mysql: $mysqldate = date( 'Ymd H:i:s', $phpdate );
• Let mysql do the work using the built-in functions: $query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) ...";

Answer 4

if you have datetime avaialable from a format like the above, you just need to use the 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; }