SQL/SSRS select first weekday on or prior to date

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) either through a custom expression or dataset. If the 20th falls on a Saturday, default to friday 19th. If the 20th falls on Sunday defaul开发者_开发问答t to Friday 18th. How do I do this and what is the best way of doing it?

How about this:

DECLARE @daytwenty date;
SET @daytwenty = DATEADD(mm, DATEDIFF(mm, 0, getdate()) ,19) --Get Twentieth Day of current month

SELECT 
      CASE DATEPART(DW,@daytwenty)
         WHEN 1 THEN DATEADD(dd, -2, @daytwenty) --When the twentieth day is a Sunday, take two days off
         WHEN 7 THEN DATEADD(dd, -1, @daytwenty) --When the twentieth day is a Saturday, take one day off
         ELSE @daytwenty --Else the twentieth day is a weekday already
      END

You should use Datetime for 2008 version. By writing

DECLARE @LatestDate date;

in previous version of Sql Server gives compilation error.

Column, parameter, or variable #1: Cannot find data type date.

So below is the code Sql server 2008

DECLARE @LatestDate datetime;
SET @LatestDate = DATEADD(mm, DATEDIFF(mm, 0, getdate()) ,19) --Get Twentieth Day of current month

SELECT 
      CASE DATEPART(DW,@LatestDate)
         WHEN 1 THEN DATEADD(dd, -2, @LatestDate) --When the twentieth day is a Sunday, take two days off
         WHEN 7 THEN DATEADD(dd, -1, @LatestDate) --When the twentieth day is a Saturday, take one day off
         ELSE @LatestDate --Else the twentieth day is a weekday already
      END

I hope this is what u want,

 select case datename(dw, getdate())
 when 'Monday' then 'Friday'
 when 'Tuesday' then 'Monday'
 when 'Wednesday' then 'Tuesday'
 when 'Thursday' then 'Wednesday'
 when 'Friday' then 'Thursday'
 when 'Saturday' then 'Friday'
 when 'Sunday' then 'Friday'
 end