List: General Discussion « Previous MessageNext Message » From: Martin Ramsch Date: September 3 1999 7:28pm Subject: Re: DATES View as plain text
```On Fri, 1999-09-03 14:09:43 -0400, ATS wrote:
> Monty,
>
> >>year(now()) - year(birth) + if(right(now(),5) >= right(birth,5),1,0) as
> age
>
> Having a problem with this one. birth is entered as 1932-12-09
> (I'm an old fart :-)
> year(now()) -year(birth) produces 67 as it should. However, I'm not yet 67.
> So I think the next part should be - if(). or if( , -1,0)

I think, it should be:
YEAR(b) - YEAR(a) - IF( RIGHT(b,5)<RIGHT(a,5), 1, 0 )

> There seems to be another problem.
>
> 09-04 => 12-09 produces True. Could it be that it's first doing 9-4  = 5
> and then 12-9 = 3 so the left side is indeed greater then the right side
> and the statement evualates to true.

In a numerical context, this is true.  Compare:

mysql> SELECT 09-04 >= 12-09;  mysql> SELECT '09-04' >= '12-09';
+----------------+             +--------------------+
| 09-04 >= 12-09 |             | '09-04' >= '12-09' |
+----------------+             +--------------------+
|              1 | (true)      |                  0 | (false)
+----------------+             +--------------------+

In the formula above, the RIGHT functions yield strings, so the
comparison is done for stings, too.

Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
```