Date/Month Sort - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Date/Month Sort

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-05 : 14:05:03

I have the following:
select distinct datename(month, monthend) + ' ' + datename(year, monthend) from snapsraw

problem is results look like this:

October 2006
July 2006
November 2006
August 2006
September 2006

How can I make it so that the months show up in the right order

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 14:47:30

select distinct datename(month, monthend) + ' ' + datename(year, monthend)
from snapsraw
order by monthend

My bad, wasn't thinking, scratch this, see jsmith8858 below.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-05 : 14:50:29

yeah, tried that and i get
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
so than i tried:

select distinct datename(month, monthend) + ' ' + datename(year, monthend)
from snapsraw
order by datename(month, monthend) + ' ' + datename(year, monthend)

and its still not putting them in the right order

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-05 : 14:56:14

Does your MonthEnd column contain only month-ending dates?

If so, then just return them:

select distinct monthend
from snapsraw
order by monthend

and then format your monthend column at your front end. Or, if you insist on formatting data in T-SQL (never a good idea), just use GROUP BY:

select monthend, datename(month, monthend) + ' ' + datename(year,monthend)
from snapsraw
group by monthend
order by monthend

If monthend has multiple dates per month other than just the ending date, then it is a little more complicated but the same basic idea.

- Jeff

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-01-06 : 04:19:09

Hi duhaas,
The way to do it is you sort it using the datepart function.You can use it like this

select blah blah order by datepart(MM,month).

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-06 : 10:36:35

Nope... if you do it just by DatePart(mm,Month), and the year changes, output will not be as expected. Jeff Smith did it right.

--Jeff Moden

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-01-07 : 01:55:53

Yes but what if we sort it by both the dateparts of month as well as the year.I guess the output will be what is expected.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 05:52:33

With this code, it doesn't matter wether the dates are only the last date of every month. Any date is calculated to the month where they belong.

select		datename(month, theMonth) + ' ' + datename(year, theMonth)
from		(
			select	distinct dateadd(month, datediff(month, 0, monthend), 0) as theMonth
			from	snapsraw
		) d
order by	theMonth

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 06:03:03

Or if you are interested in how many occurancies there are per month?

select		datename(month, theMonth) + ' ' + datename(year, theMonth) AS 'Long month name',
		theCount as 'Occurancies'
from		(
			select		dateadd(month, datediff(month, 0, monthend), 0) as theMonth,
					count(*) as theCount
			from		snapsraw
			group by	dateadd(month, datediff(month, 0, monthend), 0)
		) d
order by	theMonth

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-07 : 09:56:20

I would first find out what kind of data is stored in the MonthEnd column. If it is just month-ending dates, or the first day of the month, or something like that (as the column name implies), it will be more efficient to just return and group/sort by the values in the column.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 15:02:15

Yes, you are right.
And it seems duhaas doesn't care since it has been two days since last checkup.

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-08 : 22:48:38

quote:
Originally posted by ayamas

Yes but what if we sort it by both the dateparts of month as well as the year.I guess the output will be what is expected.

Yes, but why do it with 2 calculations when 1 will do?

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources