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

Forgot SQL Server Admin Password

exec sp_password @new='NewPassword', @loginame='sa'
go
alter login sa enable
go

How To Find The Number Of Live Connection In Database

SELECT DB_NAME(dbid) AS db, COUNT(dbid) AS connection FROM SYS.SYSPROCESSES WHERE dbid > 0
GROUP BY dbid

How To Duplicate Tab Or Clone Tab In Firefox

Just press the Ctrl key And drag The corresponding tab which is to Clone or duplicate

Supported By Firefox 3.x

How To Search Text In Script In Database In Procedure

    DECLARE @srchString VARCHAR(1000)
    SET @srchString='Procedure_Name'
    SET @srchString = '%' + @srchString + '%'    
    SELECT O.NAME FROM syscomments  C
    JOIN sysobjects O ON O.id = C.id
    WHERE O.xtype = 'P' AND C.TEXT LIKE @srchString

How To Get Time Part From datetime Sql Server

select convert(varchar,getdate(),8) AS Time

Whats the Difference between Cluster and Non-cluster index ?

Clustered Index

* There can be only one Clustered index for a table
* Usually made on the primary key
* The logical order of the index matches the physical stored order of the rows on disk

Non-Clustered Index

* There can be only 249 Clustered index for a table
* Usually made on the any key
* The logical order of the index does not match the physical stored order of the rows on disk

how to get The Business Dates between the two given dates

CREATE FUNCTION dbo.fnGetNoOfBusinessDates
(@STARTDATE datetime,@EntDt datetime)
RETURNS TABLE
AS
RETURN
 
with DateList as
 (
    select cast(@STARTDATE as datetime) DateValue
    union all
    select DateValue + 1 from    DateList   
    where  DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
 )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
GO

select * from dbo.fnGetNoOfBusinessDates(getdate(),getdate()+30);

how to get number of business days between two given dates

CREATE FUNCTION dbo.fnGetNoOfBusinessDays(@STARTDATE datetime,@EntDt datetime)
returns int
as
BEGIN
declare @dtCnt int;
;with DateList as 
 ( 
    select cast(@STARTDATE as datetime) DateValue 
    union all 
    select DateValue + 1 from    DateList    
    where   DateValue + 1 < convert(VARCHAR(15),@EntDt,101) 
 )
    select @dtCnt = count(*) from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
return @dtCnt
END
go

select dbo.fnGetNoOfBusinessDays(getdate(),getdate()+50) as NoOfBusinessDays

How To Set Vertical Scroll For the Particular Page

window.onload = function()
{
document.body.scroll = "yes";
}

Note:IE Supported

how to find the list of stored procedure in the database

Sql Server 2005

SELECT * FROM sys.procedures;

User Procedure

SELECT * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%';

how to fetch record which is not null

SELECT * FROM <table name> WHERE <column name> IS NOT NULL

How To Get The List Of Database Available In Particular Server

SELECT * FROM master.dbo.sysdatabases

How To Delete A Duplicate Records From Table

Query To Find The Duplicate Records

SELECT ColumnName1,ColumnName2,ColumnNameN,count(*) As UserDefinedColName
FROM TableName
GROUP BY ColumnName1,ColumnName2,ColumnNameN
HAVING count(*) > 0

Query To Delete Duplicate Records

select distinct * into #tmpTableName from TableName
truncate table TableName
insert TableName select * from #tmpTableName
drop table #tmpTableName

What's the difference between ISNULL & COALESCE

ISNULL()

It replace the NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the type is different.

Syntax

ISNULL ( check_expression , replacement_value )

check_expression

The expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

COALESCE()

Returns the first nonnull expression among its arguments.

Syntax

COALESCE ( expression [ ,...n ] )

expression

It'can be of any type.

Note:
ISNULL() and COALESCE() though both are equivalent, but they behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

For More Inforamtion :

ISNULL() or COALESCE()?

Performance: ISNULL vs. COALESCE

ISNULL() <> COALESCE(). Discuss

What Is th difference between Is Null & COALESCE

ISNULL (Transact-SQL)

COALESCE (Transact-SQL)

whats the difference between sp_helptext and sp_help

sp_helptext
sp_helptext displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view.

Syntax:-
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]

Example:
Exec sp_helptext proc_name.

sp_help
sp_help reports information about a database object (any object listed in the sysobjects
table), a user-defined data type

Syntax:-
sp_help [ [ @objname = ] name ]

Example:
EXEC sp_help tablename

How to check whether temp table exist

if object_id('tempdb..##temptblname') is not null
drop table ##temptblname

How to remove white space in column

SELECT LTrim(RTrim(Column_Name))AS Column_Caption FROM TableName

How To Find the Time Difference Between Two Given Time

SELECT DATEDIFF(HOUR,'2008-10-14 18:30:32.800',getdate()) as TotalHours

What's the difference between TINYINT, SMALLINT, INT and BIGINT DataType In SQL SERVER

This datatype same is type but the differ from storage type,min and max values, the details as follows,

Data Type	Min Value	Max Value	Storage Size
tinyint	0	255	1 byte
smallint	-2^15 (-32,768)	2^15 - 1 (32,767)	2 bytes
int	-2^31 (-2,147,483,648)	2^31 - 1 (2,147,483,647)	4 bytes
bigint	-2^63 (-9,223,372,036,854,775,808)	2^63 - 1 (9,223,372,036,854,775,807)	8 bytes