From: Paul DuBois Date: August 28 2007 12:04am Subject: Re: BUG: DATE_ADD 99999 fails, but 9999 works. List-Archive: http://lists.mysql.com/mysql/208749 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" ; format="flowed" At 4:56 PM -0700 8/27/07, 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! Why do you consider this extremely obnoxious? Also, I'm curious why you tried to use the user comment page to report a bug, given that the page says: "If you've found a bug or wish to request a feature MySQL is missing, please use the bug system. When doing this, please perform a search first in order not to report a bug or request a feature that's already in that system." The option is there to let you know that the user comment system isn't for reporting bugs *and* to let you know the correct place to report them. It's there as extra reinforcement for people who ignore or overlook the statement that I mentioned in the preceding paragraph. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com