Search code examples
sqlsql-serverstdevp

SQL Server - STDEVP failing on datetime column when case statement shouldn't allow it to

I have a column called RANDOMDate which is of datatype datetime. I want to preform a STDEVP across all columns only if they are of numeric content. So, if i came across RANDOMDate i would expect my case statement below to simply pass in 0 for RANDOM, below statement is just for RANDOMDate column only:

select STDEVP(CASE WHEN ISNUMERIC(CAST([DataStorage].[dbo].[DateTest].[RANDOMDate] as nvarchar(max))) = 1 THEN [DataStorage].[dbo].[DateTest].[RANDOMDate] ELSE 0 END) AS StandardDeviation
from [DataStorage].[dbo].[DateTest]

However, this fails with error:

Operand data type datetime is invalid for stdevp operator.

I was expecting that since the case statement says when ISNUMERIC of the char value then pass in the column, otherwise pass in 0, should this not take care of the issue with RANDOMDate being of datetime?

Any ideas what the problem could be? Note i have to keep STDEVP on the outside of the case statement as i require an aggregate function.

Solution

  • Not sure i understand the Return Types explanation

    A CASE expression:

    Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence (Transact-SQL).

    CASE

    SQL Server uses the following precedence order for data types:

    user-defined data types (highest) 
sql_variant 
xml 
datetimeoffset 
datetime2 
datetime 
smalldatetime 
date 
time 
float 
real 
decimal 
money 
smallmoney 
bigint 
int 
smallint 
tinyint 
bit 
ntext 
text 
image 
timestamp 
uniqueidentifier 
nvarchar (including nvarchar(max) ) 
nchar 
varchar (including varchar(max) ) 
char 
varbinary (including varbinary(max) ) 
binary (lowest)

    Data type precedence

    So this expression

    CASE WHEN ISNUMERIC(CAST([DataStorage].[dbo].[DateTest].[RANDOMDate] as nvarchar(max))) = 1 THEN [DataStorage].[dbo].[DateTest].[RANDOMDate] ELSE 0 END

    has data type of datetime since datetime has a higher precedence than int.

    So you need to add a conversion to force the CASE expression to return a type that is compatible with STDEVP, and float is probably the best choice:

    select STDEVP(CASE WHEN ISNUMERIC(CAST([RANDOMDate] as nvarchar(max))) = 1 THEN cast([RANDOMDate] as float) ELSE 0 END) AS StandardDeviation
from [DateTest]