Hello I've converted a spreadsheet into a SSRS report which is run on the 20th of month and I need to pass the 20th of the current month as a default value (start date). If the 20th falls on a Saturday, default to friday 19th. If the 20th falls on Sunday default to Friday 18th. How do I do this and what is the best way of doing it?
Personally, I would handle this within the stored procedure and not try and work this out in SSRS. This code snippet shows how to determine a startdate according to your rules. Note that in my environment the week starts on a Sunday (i.e.@@DATEFIRST = 7) - if you have a different start to the week you will need to adjust the values. declare @d datetime --set @d = '20 august 2011' -- saturday set @d = '20 November 2011' -- sunday select @startdate = case when DATEPART(dw,@d) = 7 --Saturday then @d-1 when DATEPART(dw,@d) = 1 -- Sunday then @d -2 else @d end select @startdate