I'm having error "error converting varchar to datetime" on ssrs when i try to create a dataset for report

Question

Here I created a stored procedure AvgLoans_MonthlyIncomeByMonth. Though the stored proc works on SQL when I execute it am having trouble making it work on SSRS since I need the data for a chart am creating. I get the error

error converting varchar to date

Please help where I made a mistake here..

ALTER PROCEDURE [dbo].[proc_AvgLoans_MonthlyIncomeByMonth]    --EXEC  dbo.proc_AvgLoans_MonthlyIncomeByMonth '2022/05/15'

    @ReportDate Datetime,
    @LoanAmount varchar(max)=null,
    @LoanPurpose varchar(max)=null,
    @PropertyUsage varchar(max)=null,
    @Demographics2 varchar(max)=null    
    
    AS
        BEGIN

                        
            DECLARE     @ReportDate2 datetime = '2022/05/15',
                        @DayOfWeek varchar(100),
                        @BeginOfWeek Datetime,
                        @BeginOfMonth Datetime,
                        @BeginOfYear Datetime

            SET         @DayOfWeek = (SELECT DATENAME(dw,@ReportDate2))
            SET         @BeginOfWeek =  (
                                            SELECT      CASE    WHEN @DayOfWeek = 'Sunday'      THEN DATEADD(dd,0,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Monday'      THEN DATEADD(dd,-1,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Tuesday'     THEN DATEADD(dd,-2,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Wednesday'   THEN DATEADD(dd,-3,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Thursday'    THEN DATEADD(dd,-4,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Friday'      THEN DATEADD(dd,-5,@ReportDate2)
                                                                WHEN @DayOfWeek = 'Saturday'    THEN DATEADD(dd,-6,@ReportDate2) END BeginOfWeek
                                        )

            SET         @BeginOfMonth = (
                                            SELECT      CONVERT(DATETIME,CONVERT(VARCHAR(100),DATEPART(MM,@ReportDate2)) + '/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate2)))
                                        )

            SET         @BeginOfYear = (
                                            SELECT      '1/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate2))
                                        )

                                        ---select @dayofweek,@BeginOfWeek,@BeginOfMonth,@BeginOfYear


                    SELECT      b.*,c.[property usage],d.[martial status],d.[sex],d.[race],DATEDIFF(YY,d.[date of birth],@ReportDate2) Age,[monthly income],
                                CASE WHEN [Loan Date] >= @BeginOfWeek AND [Loan Date] <= @ReportDate2 THEN 'WeekToDate'
                                 WHEN [Loan Date] >= @BeginOfMonth  AND [Loan Date] <= @ReportDate2  THEN 'MonthToDate'
                                WHEN [Loan Date] >= @BeginOfYear   AND [Loan Date] <= @ReportDate2 THEN 'YearToDate' ELSE NULL END ToDate,
                                CASE WHEN [Loan Date] >= @BeginOfWeek AND [Loan Date] <= @ReportDate2 THEN 1
                                WHEN [Loan Date] >= @BeginOfMonth  AND [Loan Date] <= @ReportDate2  THEN 2
                                WHEN [Loan Date] >= @BeginOfYear   AND [Loan Date] <= @ReportDate2 THEN 3 ELSE NULL END ToDateOrder,
                                CASE WHEN [loan amount] <= 100000 THEN 'Less Than $100k'
                                WHEN [loan amount] between 100000 AND 200000 THEN '$100k to $200k'
                                WHEN [loan amount] > 200000 THEN 'More Than $200k' END LoanAmountGroup,
                                CASE WHEN [loan amount] <= 100000 THEN 1
                                WHEN [loan amount] between 100000 AND 200000 THEN 2
                                WHEN [loan amount] > 200000 THEN 3 END LoanAmountGroupOrder,
                                CASE WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) < 25 THEN '<=25'
                                WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) between 25 and 35 THEN '26-35'
                                WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) between 36 and 45 THEN '36-45'
                                WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) > 45 THEN '46+' END AgeGroup,
                                CASE WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) < 25 THEN 1
                                 WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) between 25 and 35 THEN 2
                                 WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) between 36 and 45 THEN 3
                                 WHEN DATEDIFF(YY,d.[date of birth],@ReportDate2) > 45 THEN 4 END AgeGroupOrder
            INTO #Financials  
            FROM        [dbo].[fact_financials] a
            LEFT JOIN   [dbo].[dim_loan info] b
            ON          a.[loan ID]= b.[loan ID]
            LEFT JOIN   [dbo].[dim_property info] c
            ON          a.[loan ID] = c.[loan ID]
            LEFT JOIN   [dbo].[dim_borrower info] d
            ON          a.[loan ID] = d.[loan ID]

            
            SELECT      *
            INTO        #FinancialsII
            FROM        #Financials
            WHERE       LoanAmountGroup IN (SELECT items FROM dbo.Split(@LoanAmount,','))
            AND         [Purpose of Loan] IN (SELECT items FROM dbo.Split(@LoanPurpose,','))
            AND         [Property Usage] IN (SELECT items FROM dbo.Split(@PropertyUsage,','))
            AND         (AgeGroup IN (SELECT items FROM dbo.Split(@Demographics2,','))
                OR       [martial status] IN (SELECT items FROM dbo.Split(@Demographics2,','))
                OR       Race IN (SELECT items FROM dbo.Split(@Demographics2,','))
                OR       Sex IN (SELECT items FROM dbo.Split(@Demographics2,',')))
            
        
            
                SELECT      DATEPART(MM,[loan date]) LoanMonth,DATEPART(YY,[loan date]) LoanYear,
                            AVG(CONVERT(DECIMAL(10,2),[monthly income])) AvgMonthlyIncome,AVG(CONVERT(DECIMAL(10,2),[loan amount])) AvgLoanAmount,
                            SUM(CONVERT(DECIMAL(10,2),[monthly income]) / CONVERT(DECIMAL(10,2),[loan amount])) MonthlyIncomeVSLoanAmount
                FROM        #FinancialsII
                WHERE       [loan date] <= @ReportDate2 
                GROUP BY    DATEPART(MM,[loan date]),DATEPART(YY,[loan date])
                ORDER BY    DATEPART(YY,[loan date]) DESC,DATEPART(MM,[loan date]) DESC
            
                    
            
    END

Answer 1

This will get you there, but there is an open IF you can clean up.

=SUBSTITUTE(IF(ISNUMBER(FIND("/",A1)),SUBSTITUTE(A1," (POS) / ",","),IF(ISNUMBER(FIND("-",A1)),SUBSTITUTE(A1," (POS) - ",",")))," (APOS)","")