How to Convert a PHP Date to MySQL\'s DATE or DATETIME Format?

Converting PHP Date to MySQL Format

When working with a PHP date field, it may be necessary to convert it to the MySQL format of 0000-00-00 before inserting it into the database. This can be achieved through various methods depending on the specific format of the PHP date.

Converting Date for DATE Column Type

If the MySQL column is of type DATE, which stores only the date portion and not the time, use the following code:

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

This code converts the dash-separated date in PHP to a slash-separated format that strtotime() can correctly parse. The date is then formatted in the required MySQL format.

Converting Date for DATETIME Column Type

If the MySQL column is of type DATETIME, which stores both the date and time components, use the following code:

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

This code includes the time component in the conversion.

Alternate Method

If the PHP date does not follow a consistent format that can be converted using strtotime(), an alternative method is to use a regular expression to parse and reformat the date:

$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '-- ', $date);

This regular expression rearranges the date and time components into the required MySQL format.

The above is the detailed content of How to Convert a PHP Date to MySQL\'s DATE or DATETIME Format?. For more information, please follow other related articles on the PHP Chinese website!