sql - 如何将单个日期分为几个月

我正在尝试按月而不是单个日期汇总此数据的结果。我该怎么办？

SELECT DISTINCT
    CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)) AS Appended_SignInDate,
    GO.GeographicLocationDescription,
    COUNT(DISTINCT acd.EmployeeID) as Total_FTL

FROM
    dbACD.Detail.vwRockwellEventAgentPerformance AS ACD

LEFT OUTER JOIN
    dbEmployee.Summary.vwEmployeeHistory AS EH
    ON acd.employeeID = eh.EmployeeID
    AND ACD.SignOutDateTime Between eh.StartDate and eh.EndDate

LEFT OUTER JOIN
    dbEmployee.Config.vwGeographicLocation AS GO
    ON EH.GeographicLocationID = GO.GeographicLocationID


WHERE
    ACD.SignOutDateTime BETWEEN '2012-06-01' AND '2013-03-31'
--  AND ACD.SignoutReasonCode ='4'
    AND GO.GeographicLocationDescription is not null

GROUP BY

    CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)),
    GO.GeographicLocationDescription


ORDER BY
    CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)),
    GO.GeographicLocationDescription

最佳答案

DatePart()函数应对此起作用。

http://msdn.microsoft.com/en-us/library/ms174420.aspx

SELECT DISTINCT
    DATEPART(month, CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101))) AS Appended_SignInDate
    , GO.GeographicLocationDescription, COUNT(DISTINCT acd.EmployeeID) as Total_FTL
FROM dbACD.Detail.vwRockwellEventAgentPerformance AS ACD
LEFT OUTER JOIN dbEmployee.Summary.vwEmployeeHistory AS EH
     ON acd.employeeID = eh.EmployeeID
     AND ACD.SignOutDateTime Between eh.StartDate and eh.EndDate
LEFT OUTER JOIN dbEmployee.Config.vwGeographicLocation AS GO
     ON EH.GeographicLocationID = GO.GeographicLocationID
WHERE ACD.SignOutDateTime BETWEEN '2012-06-01' AND '2013-03-31'
 -- AND ACD.SignoutReasonCode ='4' AND GO.GeographicLocationDescription is not null
GROUP BY
     DATEPART(month, CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)))
   , GO.GeographicLocationDescription

ORDER BY
CONVERT (DATETIME, CONVERT (VARCHAR (10), SignInDateTime, 101)), GO.GeographicLocationDescription

关于sql - 如何将单个日期分为几个月，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16225823/