Today when i was working on store procedure which i need to use case statement in Order by in the select clause i have came across the following issue,
Msg 241, Level 16, State 1, Line 16Conversion failed when converting datetime from character string.
After a spending some time I found the root cause of this issue, the golden rule we have to use the same type in all the branches of case/when.
Sample Code
Execute the below sample script to recreate that issue,
DECLARE @tmpTBL table
(
ID INT IDENTITY(1,1),
SampleDT datetime
)
INSERT INTO @tmpTBL VALUES('2015-08-01');
INSERT INTO @tmpTBL VALUES('2015-08-02');
INSERT INTO @tmpTBL VALUES('2015-08-03');
INSERT INTO @tmpTBL VALUES('2015-08-04');
Declare @SordDirection CHAR(1); SET @SordDirection = 'D';
Declare @OrderBy VARCHAR(10);SET @OrderBy = 'SampleDT';
SELECT * FROM @tmpTBL
ORDER BY
CASE WHEN @SordDirection ='D' THEN 'A'
ELSE
CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT
END
END ASC,
CASE WHEN @SordDirection='A' THEN 'D'
ELSE
CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT
END
END DESC
Replace the CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT with the following line
CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))
After replacing if you execute the script, you can see the results without any issues,
Complete Script
DECLARE @tmpTBL table
(
ID INT IDENTITY(1,1),
SampleDT datetime
)
INSERT INTO @tmpTBL VALUES('2015-08-01');
INSERT INTO @tmpTBL VALUES('2015-08-02');
INSERT INTO @tmpTBL VALUES('2015-08-03');
INSERT INTO @tmpTBL VALUES('2015-08-04');
Declare @SordDirection CHAR(1); SET @SordDirection = 'D';
Declare @OrderBy VARCHAR(10);SET @OrderBy = 'SampleDT';
SELECT * FROM @tmpTBL
ORDER BY
CASE WHEN @SordDirection ='D' THEN 'A'
ELSE
CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))
END
END ASC,
CASE WHEN @SordDirection='A' THEN 'D'
ELSE
CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))
END
END DESC