Hi,

I have column with values January, February and so on. I need to perform
sort based on months instead the system sorts it by Alphabetical Order. Any
Hint?

Thanks

Manny

Manny Chohan wrote:
How about storing dates as DATETIME / SMALLDATETIME rather than
strings? If it's too late to do that then you can try:

SELECT mth
FROM tbl
ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;

David with all due respect, I'd suggest a datetime format that carries the
century.  Yes, you specify the year 2000 but we should all be developing code
that doesn't leave ambiguity because you never know what SQL Server defaults
will be in the future and more impportantly how your technique will be
applied to other situations.
    CONVERT(DATETIME,mth+' 01 2000',101)  --mm/dd/yyyy format

And, we should be thinking globally so it really should be:
    CONVERT(DATETIME,mth+' 01 2000',112)   --yyyymmdd format

Just my two cents,
Joe

Show quoteHide quote

Joe from WI wrote:
A good point.


That does work although on the face of it the string is wrong. 112
defines the format as YYYYMMDD, which is not what you have specified.
There is some implict conversion at work here so I'd stick to the 101
version because the string that's passed complies with the documented
format and behaviour for CONVERT.

Manny Chohan wrote:

Create a table with months:

Table Months
ID int NOT NULL,
MonthName varchar(20)

and do a join on that.  Normally it would be better to save the month
ID instead of the full text.

do you not have a real date? if not, then do you at least have a year as
well? if not, sorting my month number is pretty meaningless. (e.g. Jan
05 comes after Dec 04).


Manny Chohan wrote:
Show quoteHide quote

I apologize for the off-topic comment, but this one is just too good to pass:

How many times have I been asked that question - not necesarily in the same
context, but still... :)


ML

---
http://milambda.blogspot.com/

.... order by
charindex(monthnamecol+'*','January*February*March*April*May*June*July*August*September*October*November*December*')

Steve Kass
Drew University

Manny Chohan wrote:

Show quoteHide quote

Thanks. It worked.

Show quoteHide quote

Year To Date?
trying to do some weird math in an update statement
XOR and foreign key constraints
Like operator returns unexpected records
drop users connected to a database