May 12, 2011 at 6:55 pm
Hi all
This is my first post here so here we go:
Im having a little bit of truble with the following
This has been simplified but still gives the same error
DECLARE @sortStr VARCHAR(9), @Direc VARCHAR(4)
SET @sortStr = 'Name'
SET @Direc = 'DESC'
SELECT MyDate, Name FROM MyTable
ORDER BY
CASE @Direc
WHEN 'ASC' THEN
CASE @sortStr
WHEN 'MyDate' THEN MyDate
WHEN 'Name' THEN Name
END END ASC,
CASE @Direc
WHEN 'DESC' THEN
CASE @sortStr
WHEN 'MyDate' THEN MyDate
WHEN 'Name' THEN Name
END END DESC
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
MyDate datetime DataType
Name Nvarchar(100) DataType
Im sure it must be something very simple that I am missing... But I cant seem to put my finger on it
Any help or pointer would be much welcomed
May 12, 2011 at 7:14 pm
This is because of implicit conversions that happen due to the way you have written the case statements. If you rewrite it like this that would avoid the problem.
SELECT MyDate, Name FROM MyTable
ORDER BY
CASE WHEN @Direc = 'ASC' AND @sortStr = 'MyDate' THEN MyDate END ASC,
CASE WHEN @Direc = 'DESC' AND @sortStr = 'MyDate' THEN MyDate END DESC,
CASE WHEN @Direc = 'ASC' AND @sortStr = 'NAME' THEN Name END ASC,
CASE WHEN @Direc = 'DESC' AND @sortStr = 'NAME' THEN Name END DESC
May 12, 2011 at 7:29 pm
Hi Thanks for the rapid response
Ive just tried it out and although im no longer getting that error
The date ORDER is no working.
Any ideas on it?
Many thanks in advance
May 13, 2011 at 5:53 am
Dow... My mistake
I had the scalar var @sortStr set to nvarchar(9) when it should have been 25. Thanks for your help
Kind Regards
Martin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply