Order by Clause

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
MannyManny Chohan wrote:
> 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. An
y
> Hint?
> Thanks
> Manny
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 Portas
SQL Server MVP
--|||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:
> 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. An
y
> Hint?
> Thanks
> Manny|||Manny Chohan wrote:

> 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?
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.
HTH,
Stijn Verrept.|||... order by
charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Augus
t*September*October*November*December*')
Steve Kass
Drew University
Manny Chohan wrote:

>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
>|||Thanks. It worked.
"Steve Kass" wrote:

> ... order by
> charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Aug
ust*September*October*November*December*
')
> Steve Kass
> Drew University
> Manny Chohan wrote:
>
>|||I apologize for the off-topic comment, but this one is just too good to pass
:

> do you not have a real date?
How many times have I been asked that question - not necesarily in the same
context, but still... :)
ML
http://milambda.blogspot.com/|||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 cod
e
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
"David Portas" wrote:

> 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 Portas
> SQL Server MVP
> --
>|||Joe from WI wrote:
> 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 c
ode
> that doesn't leave ambiguity because you never know what SQL Server defaul
ts
> 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
>
A good point.

> And, we should be thinking globally so it really should be:
> CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
>
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.
David Portas
SQL Server MVP
--sql