MainelyDesign.com Blog

Invalid Date Value Error in MySQL

Posted on 09/09/2009 at 02:26 pm by Kevin Wentworth
Viewed 12,824 times | 0 comments

I ran into an interesting issue with valid dates and MySQL on Windows.  I wrote a CakePHP import function that basically moves MySQL data from one DB to the other.  I never thought that I would see an error related to an invalid date, but I did.

What determines if a date value is invalid (read: warning) or unacceptable (read: error)?

It turns out to be based on the sql_mode you have specified for your MySQL server.  I found this extremely thorough description of the different MySQL sql_mode options here.

In my case it was an invalid leap year date.

This is the MySQL error I got: 1292 - Incorrect date value: '2007-02-29' for column 'event_start_date' at row 1.

Damn leap years!

The database servers have different sql_mode settings, which allowed the invalid date to be inserted on one, but not the other MySQL server.

Cheers,

-Kevin