convert varchar with month into date

Question

mysql> select * from tr;
+------+
| mnt  |
+------+
| jun  |
| mar  |
| jan  |
| aug  |
+------+
4 rows in set (0.00 sec)

mysql> select * from tr1;
+------+------+
| mnt  | id   |
+------+------+
| aug  |   11 |
| jan  |   12 |
| mar  |   15 |
| apr  |   16 |
+------+------+
4 rows in set (0.00 sec)

it worked for this query..

mysql> select * from tr join tr1 on tr.mnt=tr1.mnt;
+------+------+------+
| mnt  | mnt  | id   |
+------+------+------+
| aug  | aug  |   11 |
| jan  | jan  |   12 |
| mar  | mar  |   15 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from tr2;
+------------+------+
| mn         | id   |
+------------+------+
| 2009-02-14 |   11 |
| 2009-03-03 |   12 |
| 2009-08-08 |   12 |
+------------+------+
3 rows in set (0.00 sec)

mysql> insert into tr2 values('2009-01-01',14);
Query OK, 1 row affected (0.06 sec)

but its not working for this..

mysql> select * from tr join tr2 on tr.mnt=MONTHNAME(tr2.mn);
Empty set (0.00 sec)

pls give the exact query..

Answer 1

MONTHNAME(date) returns a varchar, so "yes" - you can compare them.

Consider this syntax:

select *
from A
join B on B.InvoiceMonth = MONTHNAME(A.Mnth);

Answer 2

Since MONTHNAME() returns a string and InvoiceMonth is a string, you can compare them sanely. Therefore, within broad limits, your query is OK. You should use the modern JOIN notation, though:

SELECT *
  FROM A
  JOIN B ON B.InvoiceMonth = MONTHNAME(A.Mnth)
  JOIN C ON B.InvoiceMonth = MONTHNAME(C.Monthx)

You don't need to use all three conditions: if X = Y and Y = Z, then X = Z.

Of course, since the results for January 2011 are not really comparable with the results for January 2010, you really need to work out where the year information comes from too, and ensure that you don't mix'n'match between years.