无法从字符串转换日期

我从sql server获取季度记录.这些记录就像每年每个季度的总借记和贷记.在我的SQL查询中,我已经制作了季度,并且年份是动态的,您可以在代码中看到.

public List GetExpenseDataQuarterly(string Id, string Year)
        {
            string SQL = "select aspnetusers.username, SUM(case when Expense.Type='credit' and (Expense.Date>='@year-01-01' and Expense.Date<='@year-03-31') then Expense.Amount else 0 end) as QuarterOneCredits,";
            SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>='@year-04-01' and Expense.Date<='@year-06-30') then Expense.Amount else 0 end) as QuarterTwoCredits,";
            SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>='@year-07-01' and Expense.Date<='@year-09-30') then Expense.Amount else 0 end) as QuarterThreeCredits,";
            SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>='@year-10-01' and Expense.Date<='@year-12-31') then Expense.Amount else 0 end) as QuarterFourCredits,";
            SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>='@year-01-01' and Expense.Date<='@year-03-31') then Expense.Amount else 0 end) as QuarterOneDebits,";
            SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>='@year-04-01' and Expense.Date<='@year-06-30') then Expense.Amount else 0 end) as QuarterTwoDebits,";
            SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>='@year-07-01' and Expense.Date<='@year-09-30') then Expense.Amount else 0 end) as QuarterThreeDebits,";
            SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>='@year-10-01' and Expense.Date<='@year-12-31') then Expense.Amount else 0 end) as QuarterFourDebits";
            SQL += " from Expense inner join AspNetUsers on AspNetUsers.Id=Expense.MadeBy";
            if (Id == null)
            {
                SQL += " group by aspnetusers.username";
            }
            else
            {
                SQL += " where Expense.MadeBy=@id group by AspNetUsers.UserName group by aspnetusers.username";
            }

            using (IDbConnection cn=Connection)
            {
                cn.Open();
                List objList = cn.Query(SQL, new { year = Year, id = Id }).ToList();
                return objList;
            }
        }

在这里我得到一个例外Conversion failed when converting date and/or time from character string.可以有人告诉我它是什么意思,我怎么能摆脱它

我删除了字符串外的动态年份"@year",因为它是一个动态值.当写成'@ year-04-01'时,SQL将"@year"视为字符串而不是动态值.

使用以下代码:

public List GetExpenseDataQuarterly(string Id, string Year)
            {
                string SQL = "select aspnetusers.username, SUM(case when Expense.Type='credit' and (Expense.Date>=@year+'-01-01' and Expense.Date<=@year+'-03-31') then Expense.Amount else 0 end) as QuarterOneCredits,";
                SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>=@year+'-04-01' and Expense.Date<=@year+'-06-30') then Expense.Amount else 0 end) as QuarterTwoCredits,";
                SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>=@year+'-07-01' and Expense.Date<=@year+'-09-30') then Expense.Amount else 0 end) as QuarterThreeCredits,";
                SQL += " SUM(case when Expense.Type='credit' and (Expense.Date>=@year+'-10-01' and Expense.Date<=@year+'-12-31') then Expense.Amount else 0 end) as QuarterFourCredits,";
                SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>=@year+'-01-01' and Expense.Date<=@year+'-03-31') then Expense.Amount else 0 end) as QuarterOneDebits,";
                SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>=@year+'-04-01' and Expense.Date<=@year+'-06-30') then Expense.Amount else 0 end) as QuarterTwoDebits,";
                SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>=@year+'-07-01' and Expense.Date<=@year+'-09-30') then Expense.Amount else 0 end) as QuarterThreeDebits,";
                SQL += " SUM(case when Expense.Type='debit' and (Expense.Date>=@year+'-10-01' and Expense.Date<=@year+'-12-31') then Expense.Amount else 0 end) as QuarterFourDebits";
                SQL += " from Expense inner join AspNetUsers on AspNetUsers.Id=Expense.MadeBy";
                if (Id == null)
                {
                    SQL += " group by aspnetusers.username";
                }
                else
                {
                    SQL += " where Expense.MadeBy=@id group by AspNetUsers.UserName group by aspnetusers.username";
                }

                using (IDbConnection cn=Connection)
                {
                    cn.Open();
                    List objList = cn.Query(SQL, new { year = Year, id = Id }).ToList();
                    return objList;
                }
            }