How do I format dates in PHP for MySQL?

Question

I'm trying to insert a date in the format DD/MM/YYYY into MySQL. The database is not liking the format and is completely mangling the dates. Is there a simple fix to convert the date into the proper format?

Answer 1

$date = preg_replace('!^(\d+)/(\d+)/(\d+)$!', '$3-$2-$1', $date);

Or, if you're not too comfortable with regexen:

$date = join('-', array_reverse(explode('/', $date)));

Answer 2

INSERT INTO the_table (the_date)
VALUES (STR_TO_DATE('31/12/2001', '%d/%m/%Y'));

Format codes are documented at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Update: I guess I should also mention that MySQL will perform an automatic conversion to date when required. In such case, it'll parse the string using the format defined in the date_format or datetime_format variables:

mysql> SELECT @@date_format, @@datetime_format;
+---------------+-------------------+
| @@date_format | @@datetime_format |
+---------------+-------------------+
| %Y-%m-%d      | %Y-%m-%d %H:%i:%s |
+---------------+-------------------+
1 row in set (0.00 sec)

Answer 3

I think MySQL should be happy with YYYY-MM-DD so in php date('Y-m-d',$myDate)

Answer 4

In general, you should aim to have dates in your application stored as timestamps or date objects while they're in memory.

This makes them easy to convert into any desired format for display purposes or for inserting into the DB. It also makes date arithmetic much easier.

If you have a date in string format, whichever string format it is, you open yourself up to having to do awkward reformatting of the string whenever you need to use it for a different purpose.

MySQL uses the YYYY-MM-DD format for dates and YYYY-MM-DD HH:MM:SS format for datetimes. These can be represented in PHP using the 'Y-m-d' and Y-md h:i:s strings in the date() function.

When fetching the date back from the DB, I recommend using MySQL's UNIX_TIMESTAMP() function to fetch the date in timestamp format, so you don't have to worry about doing any string manipulation.

Hope that helps.