Friday, August 14, 2015

Select Query - Case Statement In Order by - Msg 241 - Conversion failed when converting datetime from character string Error

        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 16
Conversion 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