JSP executeQuery（）和MSMS在相同查询中的不同结果

Question

我正在使用tomcat、jsp和MSSQL2008，希望获得一些汇总数据，如每日、每周、每月和每年。从这里，我搜索了如何对来自MSSQL的这些摘要数据使用datapart（）函数。在JSP中，我使用

PreparedStatement pr = conn.prepareStatement(query);
ResultSet rs=pr.executeQuery();

在MSMS中，查询指令进行测试。

我在JSP程序中遇到了两个问题，但在MS SQL Server Management Studio中使用相同的查询可以很好地工作。

null

我的问题是：

switch (unit)
{
    case "Daily"        :
        query = "SELECT cast(datepart(dy, ProcessDate) as varchar(3)) Day, " + 
                    "cast(year(ProcessDate) as varchar(4)) Year, " + 
                    "SUM(case WHEN Warranty = 'Y' then 1 else 0 end) InWarranty, " + 
                    "SUM(case WHEN Warranty = 'N' then 1 else 0 end) OutOfWarranty, " +
                    "SUM(case WHEN Warranty = 'X' then 1 else 0 end) NAWarranty " +
                    "FROM zz_RMA_Summary " +
                    "WHERE ProcessDate BETWEEN '" + sDate + "' AND '" + eDate + "' " +
                    "group by year(ProcessDate), datepart(dy, ProcessDate) " +
                    "order by year(ProcessDate) DESC, datepart(dy, ProcessDate) DESC ";
        break;

    case "Weekly"       :   
        query = "SELECT cast(datepart(wk, ProcessDate) as varchar(2)) Week, " + 
                    "cast(year(ProcessDate) as varchar(4)) Year, " + 
                    "SUM(case WHEN Warranty = 'Y' then 1 else 0 end) InWarranty, " + 
                    "SUM(case WHEN Warranty = 'N' then 1 else 0 end) OutOfWarranty, " +
                    "SUM(case WHEN Warranty = 'X' then 1 else 0 end) NAWarranty " +
                    "FROM zz_RMA_Summary " +
                    "WHERE ProcessDate BETWEEN '" + sDate + "' AND '" + eDate + "' " +
                    "group by year(ProcessDate), datepart(wk, ProcessDate) " +
                    "order by year(ProcessDate) DESC, datepart(wk, ProcessDate) DESC ";
        break;

    case "Monthly"      :
        query = "SELECT cast(datepart(mm, ProcessDate) as varchar(2)) Month, " + 
                    "cast(year(ProcessDate) as varchar(4)) Year, " + 
                    "SUM(case WHEN Warranty = 'Y' then 1 else 0 end) InWarranty, " + 
                    "SUM(case WHEN Warranty = 'N' then 1 else 0 end) OutOfWarranty, " +
                    "SUM(case WHEN Warranty = 'X' then 1 else 0 end) NAWarranty " +
                    "FROM zz_RMA_Summary " +
                    "WHERE ProcessDate BETWEEN '" + sDate + "' AND '" + eDate + "' " +
                    "group by year(ProcessDate), datepart(mm, ProcessDate) " +
                    "order by year(ProcessDate) DESC, datepart(mm, ProcessDate) DESC ";
        break;

    case "Yearly"       :
        query = "SELECT cast(yyyyy, ProcessDate) as varchar(4)) Year, " + 
                    "SUM(case WHEN Warranty = 'Y' then 1 else 0 end) InWarranty, " + 
                    "SUM(case WHEN Warranty = 'N' then 1 else 0 end) OutOfWarranty, " +
                    "SUM(case WHEN Warranty = 'X' then 1 else 0 end) NAWarranty " +
                    "FROM zz_RMA_Summary " +
                    "WHERE ProcessDate BETWEEN '" + sDate + "' AND '" + eDate + "' " +
                    "group by datepart(yyyy, ProcessDate) " +
                    "order by datepart(yyyy, ProcessDate) DESC ";
        break;
}  

Answer 1

您的问题是由日期作为字符串传递引起的，这是错误的。

为什么要用动态sql，是强制性的吗？

您能将它与参数（属于日期类型）一起使用而不能与字符串级联使用吗？

declare @t table (dt datetime);
insert into @t values ('20170110'), ('20171001'); -- Jan, 10; Oct, 1st

set language british -- this happens when my login's default language is us_english 
select *
from @t 
where dt between '01/09/2017' and '01/01/2018' -- I'm in Europe an I want the dates starting from Sept, 1st 
---
dt
2017-10-01 00:00:00.000 -- I'm happy with correct result



set language us_english -- this happens when my login's default language is us_english 
select *
from @t 
where dt between '01/09/2017' and '01/01/2018' -- I'm in Europe an I want the dates starting from Sept, 1st 
----
dt
2017-01-10 00:00:00.000 -- quite strange since I filtered starting from September
2017-10-01 00:00:00.000