convert month number to name

because the second parameter accepts a date and you only pass the month.

Month returns the integer value (1-12) of the month, not the name.

Try DATENAME(mm, date field) instead.

Or if you want a custom name use a CASE block..

SELECT MONTH([date field]) 
   WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
   WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
   WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
   WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END

Select allOpen in new window

Source: SQL Server Calendar Table

Jim Horn,
tried your solution, I am still getting the same results, January for all the month numbers.
This is what my resultset looks like, I added a.month to show the month number as well.

a.month   monthname        cnt
1                January                       100
2                January                        76
3                January                         5
4                January                  23
5                January                        44
6                January                        91
7                January                       160
8                January                       179
9                January                        88
10                January                137
11                January                        98
12                January                        71

have you tried my last comment?

Eric Moreau, Thanks a lot! It worked!!

You don't need a CTE:

select 
	datename(month, datefield) as month, count (employee) cnt
from
	tablename
where
	paygrp = 'mycriteria'
group by
	datename(month, datefield)

Select allOpen in new window

Yes, and I accepted it as a solution, your comment came in when I was typing my response to Jim.

Just for kicks and giggles pass the date column in your select query and re-run it, just to make sure that all of your dates are not in January.

Also confirm for us that [date field] is date datatype and not varchar, and give us some sample values.

Yes, it is a datetime field.

select top 10 start_Date from mytable

start_Date
NULL
NULL
2015-06-15 00:00:00.0000000
2015-06-15 00:00:00.0000000
2015-06-15 00:00:00.0000000
2015-06-15 00:00:00.0000000
2015-09-28 00:00:00.0000000
2015-09-28 00:00:00.0000000
NULL
2015-08-10 00:00:00.0000000

Well, any month number (1-12) can also be taken as a datetime, in the sense of the explicit conversion of CAST(12 as datetime), which results in 13th January 1900. So no wonder all your months are January, no matter what initial dates you had, you have month in the range of 1 to 12 and so DATENAME(month,x) with x in that range always results in January.

You can't put a month number as 2nd parameter of the Datename function - well you can, but it is not taken as month number, it is converted to a datetime. That's the "course" of implicit conversions. And due to the fact an int number is taken as numbers of days since January 1st 1900. Yep, the 13th January still is a January date.

Just because the month number came from a date, it's not still a date, you turned the initial date into a number meaning the month of that date, but pass it as a date turns that number into days from 1st January. SQL Server is just a software, no intelligence, it doesn't notice this number came from a date and you mean the month. Numbers are not tagged with their meanings nor do they carry a unit with them. And functions do not do, what you think they do.

Bye, Olaf.

The best solution is the one by Vitor, by the way, simply computing the month name in the first place and grouping by that. It'll just not easily sort well, but you can add the month number back:

select 
	month(datefield) as month, datename(month, datefield) as monthname, count (employee) cnt
from
	tablename
where
	paygrp = 'mycriteria'
group by
	month(datefield), datename(month, datefield)
order by
        month(datefield)

Select allOpen in new window

Besides this should be limited by a where clause to data of one certain year period, if you don't want statistics on months of all years of your data. There are cases, where that would be sensible, eg avarage january temperature can be computed from january day temperatures of 50 years, that'll result in a climate average rather than an average of the current year only.

So in the end whether (weather?) to do that or not is up to you,

Bye, Olaf.

Try this to convert the month number to month name, hoping to solve your problem. Try this sample code:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

Select allOpen in new window