Lutz, this is exactly what I want,

AND t2.ref_date < dateadd(month,datediff(month,0,t1.ref_date),0)

AND t2.ref_date >= dateadd(month,datediff(month,0,t1.ref_date)-1,0)

. Thank you so much.

And thanks to others too. Now I learnt how to...

I want the result to be:

M1, 20090731, 90, good

M2, 20090831, 89, good

M3, 20090930, 67, poor

Select * from Table1

inner join table2 on table1.stock_ID=table2.stock.ID and table1.ref_date=table2.last month of ref_date

I don't know how to do: table1.stock_ID=table2.stock.ID and table1.ref_date=table2.[font="Arial"]last month[/font] of ref_date. I guess I need a function...

Datetime. Thanks.

Thanks for the prompt reply. Here is the tables.

Table 1:

stock_ID, Ref_date, stock price

M1, 20090731, 90

M2, 20090831, 89

M3, 20090930, 67

Table 2:

Stock_ID, Ref_Date, Character

M1, 20090630, Good

M2, 20090731, Good

M3. 20090831, Poor

Select *...