You can define a calculated field for the datset outside of the query using a Visual Basic.NET expression. In the Data Fields or Report Data window, right-click the dataset and add a new calculated field. Give it a name and then create an expression like the following. There is no single Visual Basic function to take a date and return the week number of a month but you can use a combination of functions and some simple math to achieve this. The following calculated field will format the date '9-8-2009' as 'September Week 2' Substitute the name of your date field for MyDate:
=Format(Fields!MyDate.Value, "MMMM") & " Week " & (Int(DateDiff("d", DateSerial(Year(Fields!MyDate.Value),Month(Fields!MyDate.Value),1), Fields!FullDateAlternateKey.Value)/7)+1).ToString
Related post on MSDN Reporting Services forum: Format Date Time in SSRS Chart
Weblog by Paul Turley and SQL Server BI Blog.