Hmmm... You have a point; the doc is not quite clear. "Illegal YEAR values are converted to 0000" My understanding is that any illegal value will be first converted into '0000', then into the corresponding year inside the 1901-2155 interval, ie 2000 Well, all this is quite logical. I have no quarrel with this behaviour. Ignatius _________________________ ----- Original Message ----- From: "Peter Beckman" <beckman@purplecow.com> To: "Ignatius Reilly" <ignatius.reilly@free.fr> Cc: <php-db@lists.php.net> Sent: Wednesday, August 27, 2003 4:13 PM Subject: Re: MySQL, PHP or ghost? > On Wed, 27 Aug 2003, Ignatius Reilly wrote: > > > Read the MySQL manual. > > Hmmm, I thought I did. 6.2.2.4: > > Illegal YEAR values are converted to 0000. > > > "0" value is interpreted as 2000. > > Your empty string is converted to an integer, thus 0. > > What confuses me (what I can't find) is why a quoted empty string "" is > converted to a quoted integer "0" for a YEAR type. Based on the manual, I > assumed an empty string is considered an Illegal YEAR and thus converted to > 0000. The empty string is NOT converted to an UNQUOTED digit 0, because > otherwise the field would be set to 0000 (the intended and expected action). > > You must specify it as a string '0' or '00' or it will be interpreted as > 0000. > > Can you point me to the correct portion of the manual that explains that? > What you explain sounds like MySQL is working as designed, but I'm a bit > embarrassed that I missed that in the manual, so I want to read up on it. > Seems a bit obscure; I've been using mysql for 4+ years and have never came > across this. > > Thanks, > Beckman > > > ----- Original Message ----- > > From: "Peter Beckman" <beckman@purplecow.com> > > > > > Seems that either I don't understand mysql, or something. > > > > > > My table, with the non-important things removed... > > > > > > mysql> explain plate; > > > > > +---------+-----------------------+------+-----+---------------------+------ > > ----------+ > > > | Field | Type | Null | Key | Default | > > Extra | > > > > > +---------+-----------------------+------+-----+---------------------+------ > > ----------+ > > > | pid | mediumint(8) unsigned | | PRI | NULL | > > auto_increment | > > > | year | year(4) | YES | | NULL | > > | > > > [...] > > > > > > So my assumption is that if I insert with year="" it should use the > > > default. Or at least 0000. > > > > > > mysql> update plate set year=NULL where pid=65; > > > Query OK, 1 row affected (0.00 sec) > > > Rows matched: 1 Changed: 1 Warnings: 0 > > > > > > mysql> select * from plate where pid=65; > > > +-----+---------+------+- > > > | pid | plate | year | > > > +-----+---------+------+- > > > | 65 | DVF0343 | NULL | > > > +-----+---------+------+- > > > > > > But if I do this: > > > > > > mysql> update plate set year="" where pid=65; > > > Query OK, 1 row affected (0.01 sec) > > > Rows matched: 1 Changed: 1 Warnings: 1 > > > > > > mysql> select * from plate where pid=65; > > > +-----+---------+------+- > > > | pid | plate | year | > > > +-----+---------+------+- > > > | 65 | DVF0343 | 2000 | > > > +-----+---------+------+- > > > > > > 2000? What? Why? Confused. PHP or Mysql fault? > > -------------------------------------------------------------------------- - > Peter Beckman Internet Guy > beckman@purplecow.com http://www.purplecow.com/ > -------------------------------------------------------------------------- - > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
