DATENAME不能与集团合作，通过

Question

我有一个返回预期数据的SQL查询：

SELECT intMonth as Month 
, intYear AS Year, 
Sum([intExposureHours]) as [Total Hours] 
    FROM tblSSQReleaseToMajor RTM 
    INNER JOIN tblCompany C On RTM.CompanyID = C.CompanyID 
    AND C.bitActive = 1 
    INNER JOIN [tblIncidentDetailByOperatorByMonth] BM ON RTM.MajorID = BM.OperatorID 
    AND BM.ContractorID = RTM.CompanyID 
     AND BM.OperatorID = 47792 
    AND BM.intYear = 2017 
     Group By intMonth, intYear 

然而，当我尝试将数字月份更改为月份名称如下：

SELECT DateName(month,intMonth) as Month 
, intYear AS Year, 
Sum([intExposureHours]) as [Total Hours] 
    FROM tblSSQReleaseToMajor RTM 
    INNER JOIN tblCompany C On RTM.CompanyID = C.CompanyID 
    AND C.bitActive = 1 
    INNER JOIN [tblIncidentDetailByOperatorByMonth] BM ON RTM.MajorID = BM.OperatorID 
    AND BM.ContractorID = RTM.CompanyID 
     AND BM.OperatorID = 47792 
    AND BM.intYear = 2017 
     Group By intMonth, intYear 

我得到这个：

更改 “分组依据” 条款 “Group By DateName(month,intMonth), intYear”

产生以下：

谁能告诉我，为什么我没有得到一月，二月，三月，四月五月？

任何援助非常感谢！

Answer 1

DATENAME只适用于实际日期。你必须做一些事情才能使其发挥作用。

SELECT DATENAME(month, DATEADD(month, intMonth - 1, CAST('2017-01-01' AS DATETIME))) as Month 
, intYear AS Year, 
Sum([intExposureHours]) as [Total Hours] 
    FROM tblSSQReleaseToMajor RTM 
    INNER JOIN tblCompany C On RTM.CompanyID = C.CompanyID 
    AND C.bitActive = 1 
    INNER JOIN [tblIncidentDetailByOperatorByMonth] BM ON RTM.MajorID = BM.OperatorID 
    AND BM.ContractorID = RTM.CompanyID 
     AND BM.OperatorID = 47792 
    AND BM.intYear = 2017 
     Group By intMonth, intYear 

编辑：对不起，回答你为什么发生这种情况的问题，请尝试以下查询：

SELECT CAST(1 AS DATETIME) -- 1900-01-02 00:00:00.000 
SELECT CAST(42919 AS DATETIME) -- is today, the 42,919th day since January 1, 1900 

的SQL Server实际存储日期为整数。当您将'1'作为日期时，它会将其视为“1900-01-02 00：00：00.000”。因此，它表示该月份是1月份的所有结果，因为您正在输入数字1-12。它认为你说的是​​1900年1月2日至13日。这就是为什么它抓住每一个1月份。