From: Rick James
Date: October 8 2012 9:52pm
Subject: RE: date-IFNULL-sum bug?
List-Archive: http://lists.mysql.com/mysql/228350
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF92565@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR


> -----Original Message-----
> From: hsv@stripped [mailto:hsv@stripped]
> Sent: Thursday, October 04, 2012 9:35 PM
> To: mysql@stripped
> Subject: date-IFNULL-sum bug?
>=20
> Can anyone explain this to me?
> The first one seems quite wrong; the rest make perfect sense.
>=20
> mysql> select ifnull(date('1900/5/3'), date('1900/01/01')) + 10000;
> +------------------------------------------------------+
> | ifnull(date('1900/5/3'), date('1900/01/01')) + 10000 |
> +------------------------------------------------------+
> |                                                11900 |
> +------------------------------------------------------+
> 1 row in set (0.00 sec)
>=20
> mysql> select ifnull(date('1900/5/3'), date('1900/01/01'));
> +----------------------------------------------+
> | ifnull(date('1900/5/3'), date('1900/01/01')) |
> +----------------------------------------------+
> | 1900-05-03                                   |
> +----------------------------------------------+
> 1 row in set (0.00 sec)
>=20
> mysql> select date('1900/5/3') + 10000;
> +--------------------------+
> | date('1900/5/3') + 10000 |
> +--------------------------+
> |                 19010503 |
> +--------------------------+
> 1 row in set (0.00 sec)
>=20
> mysql> select date(date('1900/5/3') + 10000);
> +--------------------------------+
> | date(date('1900/5/3') + 10000) |
> +--------------------------------+
> | 1901-05-03                     |
> +--------------------------------+
> 1 row in set (0.00 sec)
>=20
> (5.5.8 under muSoft Windows)
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql