Order by Date with DateName - 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

Order by Date with DateName

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2011-01-10 : 18:42:29

Hi Guys

I gave the following select statement:


SELECT 
DateName(month,dates) AS MyMonth,
COUNT(*) AS Interviews
FROM Rooms
where YEAR(dates) = 2010
group by DATENAME(month,dates)
order by  MyMonth

which returns the following:

MyMonth Interviews
April 774
August 773
December 245
February 684
January 604
July 836
June 780
March 1070
May 745
November 978
October 873
September 708

How can I order by month "value" and not alphabetical value of month, Example:

MyMonth Interviews

January 604
February 684
March 1070
April 774
May 745
June 780
July 836
August 773
September 708
October 873
November 978
December 245

Thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-01-10 : 19:13:50

Try something like this:


DECLARE @year_number INT;
DECLARE @year DATETIME;
DECLARE @next_year DATETIME;

SET @year_number = 2010;
SET @year = CAST(@year_number AS CHAR(4)) + '0101';
SET @next_year = DATEADD(YEAR, 1, @year);

SELECT DATENAME(MONTH, dates) AS MyMonth, COUNT(*) AS Interviews
  FROM (SELECT DATEADD(MONTH, DATEDIFF(MONTH, @year, dates), @year) AS dates
          FROM Rooms
         WHERE dates >= @year
           AND dates < @next_year) AS R
 GROUP BY dates
 ORDER BY dates;

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-10 : 20:58:13

You could just do:

SELECT 
DateName(month,dates) AS MyMonth,
COUNT(*) AS Interviews
FROM Rooms
where YEAR(dates) = 2010
group by DATENAME(month,dates)
order by DatePart(month,dates)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-11 : 01:13:50

[code]SELECT
DateName(month, dates) AS MyMonth,
COUNT(*) AS Interviews
FROM dbo.Rooms
where dates >= '20100101' and dates < '20110101'
group by DATENAME(month, dates), datepart(month, dates)
order by datepart(month, dates)
[/code]

N 56°04'39.26"
E 12°55'05.63"

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-11 : 01:24:40

select DATENAME(month,dates) as MyMonth,COUNT(1) as Interviews
from dbo.Rooms
where YEAR(dates)=2010
Group by DATENAME(month,dates),MONTH(dates)
order by MONTH(dates)

Cheers!
MIK

SQLNoob81
Starting Member

38 Posts

Posted - 2011-01-11 : 05:02:18

Thanks a Mil Mik_2008, that worked!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources