SQL Server : query to return month name instead of number

Question

My goal is to return a row of month name with a column of Year. This is to get the sum of sales of one particular tenant in a per year and month table.

I have started to this code

select   
    month(date) month,
    isnull(sum(case when year(DATE) = 2015 then sales end), 0) as '2015'
from 
    tblSales
where 
    tenantcode = 'cmbina13'
group by  
    month(date)
order by 
    month(date)

and I get back this result:

The figures are correct. However I want to make month column turn into month name. Like this image below as desired output

Take a look at this http://stackoverflow.com/questions/185520/convert-month-number-to-month-name-function-in-sql — Sateesh Pagolu, Aug 07 '15 at 03:49
And why would you honestly want to do something like that? Let your presentation layer handle the presentation stuff. — lc., Aug 07 '15 at 03:51
I agree, it is a better practice to handle the naming in presentation layer unless this is for purely reporting purposes. — Sateesh Pagolu, Aug 07 '15 at 03:55
I understand, but there are instances where we would need to look at the back report, so it is needed to be done that way. But I already have code for doing the conversion in my presentation layer such as in ASP.NET and Crystal Report, way too easier than in SQL, since I have limited knowledge in it. — rickyProgrammer, Aug 07 '15 at 05:31

Pரதீப் · Accepted Answer · 2015-08-07T03:55:08.340

4

Instead of Month function use Datename function with Month datepart

select  datename(month,date) [month]
       ,isnull(sum(case when year(DATE) = 2015 then sales end), 0) as '2015'
from tblSales
where tenantcode = 'cmbina13'
group by  datename(month,date)
Order by DATEPART(MM,datename(month,date)+' 01 2011')

edited Aug 07 '15 at 03:55

answered Aug 07 '15 at 03:49

Pரதீப்

91,748
19
131
172

errror sir: Column 'tblSales.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – rickyProgrammer Aug 07 '15 at 03:51
@rickyProgrammer - check now – Pரதீப் Aug 07 '15 at 03:53
Yes it worked, but there are ordering issues. It should be in right order from Jan - Dec – rickyProgrammer Aug 07 '15 at 03:54
@rickyProgrammer Stack Overflow is not give-me-teh-codez. You should be smart enough to resolve that error, or frankly go hire somebody. – lc. Aug 07 '15 at 03:54
@rickyProgrammer - check now updated and Ic said is true you can figure out simple errors – Pரதீப் Aug 07 '15 at 03:55
@lc , I think I did what you have said, that is why i started on something FROM NOTHING :) – rickyProgrammer Aug 07 '15 at 03:56
Thanks so much @Indian for your generosity! God bless your heart :) Person like you deserves more and more , as you are not limiting yourself in giving also to others :) – rickyProgrammer Aug 07 '15 at 03:57
@sqluser - Yes best comment to start my day :) – Pரதீப் Aug 07 '15 at 04:30
already accepted this answer. regarding giving the best comment, yes you deserve that, just felt that we need more of that than those negative comments @lc . stackoverflow is really helpful in correcting errors from something we initially coded. very informative thanks to the experts. really. :) – rickyProgrammer Aug 07 '15 at 05:28

score 1 · Answer 2 · answered Aug 07 '15 at 04:01

1

SELECT      DATENAME(mm, date) AS MName
            ,ISNULL(SUM(CASE WHEN YEAR(GETDATE()) = 2015 then sales end), 0) AS '2015'
FROM        tblSales
WHERE       tenantcode = 'cmbina13'
GROUP BY    DATENAME(mm, date)
order by    DATENAME(mm, date)

answered Aug 07 '15 at 04:01

Gehan Fernando

1,221
13
24

This is going to arrange the months in alphabetical order – Pரதீப் Aug 07 '15 at 04:05
yes, it is. thanks though for answering too.. marked this also as an useful answer, for other's needs reference when they need this format. – rickyProgrammer Aug 07 '15 at 05:26

SQL Server : query to return month name instead of number

2 Answers2