无法从字符串转换日期

本文关键字：转换 日期 字符串 | 更新日期: 2023-09-27 18:28:47

我正在从sql server获取季度记录。这些记录就像一年中每个季度的借贷总额。在我的sql查询中，我创建了季度和年份，正如您在代码中看到的那样，它们是动态的。

public List<QuarterlyExpenseList> 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<QuarterlyExpenseList> objList = cn.Query<QuarterlyExpenseList>(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<QuarterlyExpenseList> 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<QuarterlyExpenseList> objList = cn.Query<QuarterlyExpenseList>(SQL, new { year = Year, id = Id }).ToList();
                    return objList;
                }
            }
相关文章：