Share it!

MySQL and Date Formatting

MySQL database engines support  a number of different date and time column formats. These can be summarized as follows:

  • DATE – Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.
  • DATETIME – Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59
  • TIMESTAMP – Similar to DATETIME with some differences depending on the version of MySQL and the mode in which the server is running.

One little known fact about MySQL dates is that whilst it is most common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter it is possible to use any character, or no character between the date and time segments. For example, the following formats all achieve the same result:

2008-10-23 10:37:22
2008/10/23 10.37.22

Managing Dates Formats

When importing data into a database, one of the most common problems you will face is unreliable date formatting. You can certainly modify dates by hand but, for large amounts of data, it will take forever (and a bit longer). With this in mind, here’s a bit of PHP code that will convert dates from different formats into a MySQL-compatible date string.

The Solution

This piece of code takes advantage of regular expressions to detect the incoming dates format and using PHP string manipulation functions. After that, this function converts the detected dates into a MySQL-compatible DATE format. In other words, it converts dates from their original format into MySQL’s YYYY-MM-DD format.

* Convert date format.
* Use regular expressions to detect dates and convert the detected dates into a MySQL-compatible DATE format.
* @link
function convert_date_format($old_date = '')
$old_date = trim($old_date);
if (preg_match('/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/', $old_date)) // MySQL-compatible YYYY-MM-DD format
$new_date = $old_date;
elseif (preg_match('/^(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])-[0-9]{4}$/', $old_date)) // DD-MM-YYYY format
$new_date = substr($old_date, 6, 4) . '-' . substr($old_date, 3, 2) . '-' . substr($old_date, 0, 2);
elseif (preg_match('/^(0[1-9]|[1-2][0-9]|3[0-1])-(0[1-9]|1[0-2])-[0-9]{2}$/', $old_date)) // DD-MM-YY format
$new_date = substr($old_date, 6, 4) . '-' . substr($old_date, 3, 2) . '-20' . substr($old_date, 0, 2);
else // Any other format. Set it as an empty date.
$new_date = '0000-00-00';
return $new_date;

This was taken from a recent project, Since data came from different sources, dates came in different formats. When imported into the database, they triggered errors almost every time. That’s because date formatting is rather strict in MySQL. And that is why we used this function was used to “standardize” incoming dates into an acceptable format.

Share it!