List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 11 1999 9:36am
Subject:Re: concat problem
View as plain text  
On Mon, 1999-10-11 10:39:26 +0300, Bogdan Paduraru wrote:
> I'm trying to do the following : I have one table with 2 int(3)
> fields, one representing the month of year and the other one
> representing the day of the month. I would like to receive using
> concat those records that have the date bigger than yy.mm.dd. and
> they are or for a specific user like in the following statement :
> 
> select * from table where name_user like 'broker' and
> concat('1999','-',month,'-',day)>1999-09-01;
> 
> Instead receiving all the records younger than September, 1st I
> receive all the records having day bigger than 1.

There are two problems:
- The left side of the comparison is a string, the right side is
  a number (1999 - 9 - 1 = 1989).
  Therefore MySQL tries to convert the left side into a number,
  which always yields 1999, and compares both sides as numbers:
     1999 > 1989,  this is always true!

- But even, if you write
     concat('1999','-',month,'-',day) > '1999-09-01'
  this won't work.
  Now a problem appeares if month has a value with only a single
  digit, because then the result of concat for example is  '1999-1-31'.
  But when compared as strings this always is greater than '1999-0...',
  regardless of the day part!

First solution that comes to mind is to format both month and day
as 2 digit numbers, e.g.
   CONCAT('1999-', LPAD(month,2,'0'), '-', LPAD(day,2,'0')) > '1999-09-01'

Better solution is to compare numbers:
   19990000+month*100+day > 19990901

(And this is what Monty recently suggested, too.)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
concat problembpaduraru11 Oct
  • Re: concat problemMartin Ramsch11 Oct
  • Re: concat problembpaduraru14 Oct