ordering on date - 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

ordering on date

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-16 : 10:57:10

order by CONVERT(VARCHAR,YEAR(A.ActiveDate)) + '-' + convert(varchar,month(a.activedate)) desc ,
CONVERT(VARCHAR,datename(month,A.ActiveDate)) + ' ' + CONVERT(VARCHAR,YEAR(A.ActiveDate)) desc

I am getting October below September
2008-9,
2008-8,
2008-7,
2008-10

Why is this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-16 : 11:00:55

because it's not numeric.

Planning replaces chance by mistake

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-16 : 11:01:03

Why dont you just use Order by A.ActiveDate DESC?

Madhivanan

Failing to plan is Planning to fail

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-16 : 11:26:50

SELECT distinct
CONVERT(VARCHAR,YEAR(A.ActiveDate)) +'-'+ convert(varchar,month(a.activedate)) sortdate,

CONVERT(VARCHAR,datename(month,A.ActiveDate)) + ' ' + CONVERT(VARCHAR,YEAR(A.ActiveDate)) ActiveDate

FROM
Art A,
ArtGroup GL
WHERE
A.ArticleID = GL.ArticleID and
GL.GroupID = @GroupID
GROUP BY A.ActiveDate
order by
CONVERT(VARCHAR,YEAR(A.ActiveDate)) +'-'+ convert(varchar,month(a.activedate)) desc,

CONVERT(VARCHAR,datename(month,A.ActiveDate)) + ' ' + CONVERT(VARCHAR,YEAR(A.ActiveDate)) desc

There is a distinct in the select

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-16 : 12:02:54

Is there an alternative way of doing this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:04:34

Seems like what you want is this:-

SELECT datename(YEAR,Date) +'-'+  datename(month,Date) sortdate, 				
datename(month,Date) + ' ' +  datename(year,Date) ActiveDate
FROM
(
SELECT DATEADD(mm,DATEDIFF(mm,0,A.ActiveDate),0) AS Date
FROM  Art A,
ArtGroup GL
WHERE	A.ArticleID = GL.ArticleID  
and	GL.GroupID  = @GroupID
GROUP BY DATEADD(mm,DATEDIFF(mm,0,A.ActiveDate),0)
)t
order by Date desc

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-16 : 13:26:28

Thank You Sir.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:33:28

quote:
Originally posted by BendJoe

Thank You Sir.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources