SSRS CASE STATEMENT

Question

I just need the case statement syntax in SSRS for the case statement below to be assigned it to a SSRS variable , @TEDate will be the variable,@effectivedate will be the parameter

declare @effectivedate date = '02/28/2022'
declare @TEDate date
set @TEDate = case when month(@effectivedate) % 3 = 0 and day(@effectivedate) = day(EOMONTH(@effectivedate)) then @effectivedate
else DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @effectivedate), 0)) end
select @TEDate

Answer 1

Hi @Rahul Polaboina ，
I did a local test,you can take a reference.
1.create a parameter named "parameterdate" ,type is TEXT ,give it an available value "02/28/2022"

2.set the dateset using the parameter

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Rahul Polaboina ,

I did a local test ,here is my steps:

1. create a parameter choosedate , Date type ,add Avalable values like =CDate("2/28/1998")

2. then add a textbox in report ,add expression like =Code.Addfunction(Parameters!choosedate.Value)
Below is the function:

Function Addfunction(currentdate as  Date) As String  
Dim referdate As Date = #1/1/1900#  
Dim year As Integer =  DatePart(DateInterval.Year, currentdate)  
Dim month as Integer =  DatePart(DateInterval.Month, currentdate)  
Dim currentday as Integer =  DatePart(DateInterval.Day, currentdate)  
Dim lastday as Integer = DatePart(DateInterval.Day, DateSerial(year, month+1, 0))  
  
Dim ss as Date   
If month Mod 3 = 0 And currentday = lastday  Then  
ss =  currentdate  
Else    
 ss = DateAdd("d", -1, DateAdd("q", DateDiff("q", referdate ,currentdate), referdate ))  
End If  
Return CStr(ss)   
End Function  

Best Regards,
Isabella