Friday, January 21, 2011

MySQL Warning - Out of range value for DATETIME column 'expire' at row 1

Problem:
While writing a web site I found I needed to update a field in a table with the current date plus 3 days.

Query:
UPDATE `user` SET `expire` = 'DATE_ADD(NOW() + INTERVAL 3 DAY)' WHERE `email` = 'email@domain.com';

mySQL Warning:
Warning 1264: Out of range value for column 'expire' at row 1

Solution:
After reading some I found that the solution was to remove the quotation marks around the DATE_ADD() method. It make sense since the method is run by mySQL and the quotation marks mean for mySQL to interpret this as a string.

Note: You can also remove the DATE_ADD method altogether, mySQL understands how to do the math by the addition sign.