Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-06-28 : 05:25:27

Hi,

I have the following command;


SELECT Month(R.date)as Month, YEAR(R.date) as Year, COUNT(*) as Total
FROM dbo.aspnet_cprdContacts
GROUP BY MONTH(R.date),YEAR(R.date)

Result:

Month Year Total

3 2012 62
4 2012 146
5 2012 81

I want the result to be;

Month Year Total

March 2012 62
April 2012 146
May 2012 81

Convert month number to month name..

Thank you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-28 : 05:30:27

either join to a MonthDescription table on the month number or format it in the display layer.

Or use a CASE statement... but that one is a hack.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-06-28 : 05:32:10

I dont want to link to MonthDescription table option - want to code it within the select statement

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-28 : 05:54:05

Sigh...


DECLARE @date DATE = GETDATE()

SELECT CASE MONTH(@date)
	WHEN 1 THEN 'Jan'
	WHEN 2 THEN 'Feb'
	WHEN 3 THEN 'Mar'
	WHEN 4 THEN 'Apr'
	WHEN 5 THEN 'May'
	WHEN 6 THEN 'Jun'
	WHEN 7 THEN 'Jul'
	WHEN 8 THEN 'Aug'
	WHEN 9 THEN 'Sep'
	WHEN 10 THEN 'Oct'
	WHEN 11 THEN 'Nov'
	WHEN 12 THEN 'Dec'
	END

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-06-29 : 03:17:41

SELECT DATENAME(m, R.date)as Month, YEAR(R.date) as Year, COUNT(*) as Total
FROM dbo.aspnet_cprdContacts
GROUP BY DATENAME(m, R.date),YEAR(R.date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources