Printed 2017/07/27 11:07AM

Format Date as Week of Month

By pturley, 2010/03/11

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.
Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.