CODEWITHSUNDEEP
sql
Rani Radcliff
Rani Radcliff

Reputation: 5076

DATENAME not working with Group By

I have a SQL query that returns data as expected:

 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

enter image description here

However, when I try to change the numerical Month to the Month Name like this:

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

I get this:

enter image description here

Changing the "Group By" Clause to "Group By DateName(month,intMonth), intYear"

Produces this:

enter image description here

Can anyone tell me why I am not getting January, February, March, April May?

Any assistance is greatly appreciated!

Upvotes: 1

Views: 268

Answers (1)

justiceorjustus
justiceorjustus

Reputation: 1965

DATENAME only works with actual dates. You have to do something hacky to get this to work.

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

Edit: Sorry, to answer your question of why this is happening, try these queries:

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 actually stores dates as integers. When you put '1' as a date, it sees that as "1900-01-02 00:00:00.000". Therefore it says the month is January for all of your results because you're inputting the numbers 1-12. It thinks you're saying January 2-13, 1900. That's why it grabs January for each one.

Upvotes: 1

Related Questions