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
Thread
DATES(ATS)2 Sep
  • Re: DATESMartin Ramsch2 Sep
  • DATESMichael Widenius3 Sep
    • Re: DATES(ATS)3 Sep
      • Re: DATES(ATS)3 Sep
        • Re: DATESMartin Ramsch3 Sep