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
> 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.)
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7