how to order in month column - 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

how to order in month column

Author

Topic

rrv62
Starting Member

1 Post

Posted - 2007-11-15 : 06:09:25

hi,

table ab contain two column
no dispaymonth
1 April
2 jan
3 Mar

when i using order by in Month column it will return Alpha/- order(April,Mar,jun like that) but i need Jan,Feb,March order

need that Query

Advance thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 06:36:48

is displaymonth just stored as a string? do you actually have a datetime field in your table that you could base the 'order by' on? as in ORDER BY month(datecolumn)

if not try a case statement in your order by...

order by
case
when displaymonth = 'jan' then 1
when displaymonth = 'feb' then 2
...
...
etc...

Em

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-15 : 15:51:41

Maybe this?

DECLARE @AB TABLE (ID INT, DisplayMonth VARCHAR(12))

INSERT @AB
SELECT 1, 'April'
UNION ALL SELECT 2, 'jan'
UNION ALL SELECT 3, 'Mar'

SELECT ID, DisplayMonth
FROM @AB
ORDER BY DATEPART(MONTH, CAST('1900-' + DisplayMonth + '-01' AS DATETIME))

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 19:30:59

Off topic, but this reminded me of schmoe at work who, in an application back end on one of our servers had a two column control table consisting of month numbers and the text of that month. He would link to a calculated column in the table (formula : month(filedate) ) so he could display the month name in his query.

To his credit, both columns were Indexed (separately).<choke><cough>

He is now employed elsewhere.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources