Order by Month Year concatenated

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

Transact-SQL (2000)

Order by Month Year concatenated

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-05 : 16:38:53

I have a date table that has month and year that I can retrieve. I want to order by month and date combination for the report.
I want Oct-03,nov-03,... followed by Jan-04
I am formatting it as follows..

SELECT
avg(cast(t1.R1 as money)) r,
(left(td1.monthdesc,3)+'-'+ + right(td1.year,2)) Monthyear,
sum(t1.rc) NumberOfR
FROM
t1 INNER JOIN td1 ON
.............

but I cannot order by the MonthYear field as it is ordering in alphbetical order.

Any ideas?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 16:52:16

HOW about the following order by statement:

ORDER BY convert(datetime,'01-' + (left(td1.monthdesc,3) + '-' + right(td1.year,2)),103) ASC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-06 : 10:32:23

WHY must people store dates as text !!!!????

Argghh !!

store it as a date type, and you can format and sort on it anyway you like ...

- Jeff

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-08 : 09:08:26

Perfect, thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources