I agree it's unfortunate that the dates get stored. But some do seem to prefer it this way.
To quote the manual at the bottom of: http://www.mysql.com/doc/en/Using_DATE.html If the date cannot be converted to any reasonable value, a 0 is stored in the DATE field, which will be retrieved as 0000-00-00. This is both a speed and convenience issue as we believe that the database's responsibility is to retrieve the same date you stored (even if the data was not logically correct in all cases). We think it is up to the application to check the dates, and not the server. Interestingly, use of date_add() and date_sub() on 'odd' dates such as "Feb 31" does produce sane results. Subtract one from "2000 Feb 31", and you'll get 2000-03-01. TO test the format of an input date, you might try: mysql> select date_add('2000-11-31', interval 0 day); +----------------------------------------+ | date_add('2000-11-31', interval 0 day) | +----------------------------------------+ | 2000-12-01 | +----------------------------------------+ 1 row in set (0.00 sec) You'll be able to tell whether you've got a good date by comparing the results. And you can "correct" a weird-but-valid date by updating it with this technique. We may not like the way it works, but at least it's correct per the manual. > -----Original Message----- > From: Hans van Harten [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 21, 2003 3:05 PM > To: [EMAIL PROTECTED] > Subject: Re: MySQL running out of date > > > Adam Clauss and Rajesh Kumar wrote: > >> Hans van Harten unknowingly asked us: > LOL > >>> Some make the laughing stock of MySQL with this code: > >>> create database data_test ; > >>> use data_test; > >>> create table test3 (a date); > >>> insert into test3 values (-1); > >>> insert into test3 values ('1996-02-31'); > >>> insert into test3 values ('1996-67-31'); > >>> select * from test3; > >>> I ran it on MYSQL-4.10-max and was not amused. > >>> Anyone to comment?? > >> Yeah, what's wrong with this? Absolutely expected results. > > Same here. > > Got 0's for the invalid dates, correct date for the other. What is > > wrong? > > I'ld say, NONE of them is correct ... Februari _31_st !! > Then I'ld expect at least a warning, rather an error, on each > of the samples! > > HansH > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
