Month Name

The SQL statement below works to provide the number of help desk requests submitted per month for help_company_id = 1.

The problem is that the months are return as numbers (1 for January). I tried using the DATENAME function but all the months were return as January. This is how I tried to use it: SELECT DATENAME(month, MONTH(help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests)

How do I convert the month numbers to month names?

SELECT MONTH(help_date_submitted) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY Month

Hi,

You should pass the current date to the DATENAME function as follows:

SELECT DATENAME(mm, GETDATE())

|||

Assuming that this application does not need to be localized, and the SQL Server is running in the language you want it output then:

SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests

will work for you.

|||

When I try SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests the following error is returned:

Column help_requests.help_date_submited is invalid in the list because it is not contained in either an aggregate function or the group by clause.

|||

SELECT MONTH(help_date_submitted) AS MonthNo,MIN(DATENAME(month,help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY MonthNo

Not exactly straight forward, but it'll work. Problem is that SQL Server doesn't see the month number (returned by the month function), and the month name being related. So you have to play some games with it, especially since you want it ordered by the month number rather than the month name.

|||

Actually, as long as you focus on the grouped values you can do it. Here is a sample query to demonstrate:

1select2datename(3month4 ,dateadd(5month6 ,month( crdate ) - 17 , 0 ) ) [Month]8 ,count(*) CountOfMonth9from10sysobjects11group by12 month( crdate )13order by14 month( crdate )

So, using this logic for the user's query you would have

1select2datename(3month4 ,dateadd(5month6 ,month( hr.help_date_submitted ) - 17 , 0 ) ) [Month]8 ,count( hr.help_request_id ) Requests9from10 help_requests hr11where12 hr.help_company_id = 113group by14 month( hr.help_date_submitted )15order by16 month( hr.help_date_submitted )

A couple of other points - I am sure you know, but this does group multiple years into each month. Is that the desired behavior? ( Just asking ).