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 protected]';

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.

1 comment:

Melvin said...

Thanks for posting this. I had a similar issue and had to pick my brain for a while. I was using NOW() within single quotes and got the same error.