Grouping a recordset by month

Hi, i'm trying to return a list of hyperlinked news items on my site. Everything works fine, apart from the fact I want to split the items by month, printing the month name then displaying the records belonging to that month. I think i've had a mental block here as i'm sure it shouldn't be too difficult. The only way i can see to do it is to create another query within the main while loop, which takes the current month and tries to query the database for all the records in that month. This would involve checking the current month (created using the mid function) against the date column in the database, which would then involve using an SQL substring function as the date is in dd/mm/yyyy format......am I over-complicating a really simple problem here or is this the only way to do it?

Grateful for any help!

this is the current code (without the nested loop)

'Get list of stories, hyperlink to each item
rsMenu.Open "select * from latest_news where status is null order by id desc", dbConn, 1
Response.Write("<ul>")

dim the_date, the_year, the_month, month_text

while not rsMenu.eof
'organise by date
the_date = rsMenu("date")
the_year = mid(7,4)
the_month = mid(the_date, 4,2)

Select Case the_month
case "01"
month_text = "January"
case "02"
month_text = "February"
case "03"
month_text = "March"
case "04"
month_text = "April"
case "05"
month_text = "May"
case "06"
month_text = "June"
case "07"
month_text = "July"
case "08"
month_text = "August"
case "09"
month_text = "September"
case "10"
month_text = "October"
case "11"
month_text = "November"
case "12"
month_text = "December"
end select

'Print out the month, then loop through the stories in that month
Response.Write("<h3>" & month_text & "</h3><br/>")

Response.Write("<li><a href = 'news.asp?id=" & rsMenu("id") & "'>" & rsMenu("title") & "</a></li>")

rsMenu.movenext
wend

Response.Write("</ul>")

why not let sql do the heavy lifting?

'Get list of stories, hyperlink to each item
rsMenu.Open "select * from latest_news where status is null order by datepart(month, date), id desc", dbConn, 1

Thanks shyam - i wasn't aware of the datepart function, very handy indeed!