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)  Problem with ORDER BY CASE

Author  Topic  kgruber Starting Member 6 Posts Posted - 2001-06-25 : 14:54:27 In my stored procedure I have an ORDER BY CASE clause that is working strangely. SELECT blah blah ORDER BY CASE WHEN @sort=1 THEN dateCol1 WHEN @sort=2 THEN dateCol2 WHEN @sort=3 THEN charCol1 WHEN @sort=4 THEN charCol2 ELSE charCol3 END This only works when @sort equals 1 or 2. When its equal to 3 or 4 I get an error message "Syntax error converting character string to smalldatetime data type." When I take out the cases with smalldatetime data everything is fine and it orders correctly. Why is this happening?