Dynamic "Order By" conversion error

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

SQL Server Development (2000)

Dynamic "Order By" conversion error

Author

Topic

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-02-06 : 11:58:55

Can anyone tell me why I'm getting a data type conversion (error listed below code) on this "Order by" operation?

declare @order_by char(5)
set @order_by = 'ASSBY'

select consumed_date (datetime), part_id1 (varchar 20), part_id2 (varchar 20)
from consumed_parts cp with(nolock)
order by (CASE @order_by
WHEN 'ASSBY' THEN part_id1)
WHEN 'RAWPP' THEN part_id2
ELSE consumed_date
END)

Server: Msg 241, Level 16, State 1, Line 26
Syntax error converting datetime from character string.

I've tried doing a convert to varchar on all and it works but then the date sort isn't correct.

Anyone ever dealt with this before?

www.beyonder422.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 13:09:26

There is a discussion about this in the comments section of an article "dynamic order by"
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Be One with the Optimizer
TG

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-02-06 : 17:27:05

that article was what I was looking for.

thanx

www.beyonder422.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources