2

I have a requirement in ssrs report to display previous business day date in expression.I am using the below sql in sql server to display date

DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                    WHEN 'Sunday' THEN -2 
                    WHEN 'Monday' THEN -3 
                    ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

can some help me with the similar code to display previous business day(just date) in ssrs expression ?

SqlLearner
  • 763
  • 8
  • 23
  • 37

1 Answers1

9

Something like this is functionally equivalent in SSRS:

=DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
        ,DatePart(DateInterval.WeekDay, Today()) = 1, -2
        ,True, -1)
    , Today())

Or even:

=DateAdd("d"
    , Switch(DatePart("w", Today) = 2, -3
        ,DatePart("w", Today) = 1, -2
        ,True, -1)
    , Today)

Which is the same but with a few syntax shortcuts.

Depending on the region you are in, you may need to either call the date functions with a start or week parameter or adjust the numbers in the expression, but the above works fine for me in a UK environment.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92