From: Chris
Date: August 28 2007 12:44am
Subject: Re: BUG: DATE_ADD 99999 fails, but 9999 works.
List-Archive: http://lists.mysql.com/mysql/208752
Message-Id: <46D37005.8070900@leftbrained.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

I don't think this is a bug. I think what's happening is that your 
timestamp column can't hold that date, it's max value is somewhere in 2038.

So I guess either change your timestamp column to a datetime column, or 
prevent users from putting invalid data in.


Daevid Vincent wrote:
> using 99999 as the DATE_ADD interval value will result in 000-00-00 but
> one less 9 will work.
>
> root# mysql --version
> mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine
> wrapper
>
> CREATE TABLE `Users` ( 
> 	`CoreID` int(10) unsigned NOT NULL auto_increment,
> 	`Username` varchar(155) default NULL,
> 	`Password` varchar(64) default NULL, 
> 	`password_expire` timestamp NOT NULL default '0000-00-00
> 00:00:00',
> 	PRIMARY KEY (`CoreID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9999
> DAY) WHERE CoreID = 1 LIMIT 1;
>
> UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL
> 99999 DAY) WHERE CoreID = 1 LIMIT 1;
>  
>
> Added as bug:
> http://bugs.mysql.com/bug.php?id=30656
>
> Added as a tip:
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
>
> BTW, it's extremely obnoxious that when I enter in a comment on that
> page, then choose "bug" from the select box, it throws me to another
> page that says, "Sorry, but this is not the correct place to report
> bugs. You need to report bugs using our online bug reporting system. You
> can start filling out a bug report with the text you have already
> entered by clicking the following button:" ... Well why the heck did you
> let me choose that option then! UGH! 
>
>
> ÐÆ5ÏÐ 
>
>
>
>