SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Format Date as Week of Month

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.


No comments.

Leave a Comment

Please register or log in to leave a comment.