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

Question

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.

See "Return Types" section in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017 for explanation why it is failing. Why cant you simply exlude non numeric columns from your select instead of adding `CASE WHEN` to them? — Alex, Sep 19 '18 at 23:01
I can't exclude non numeric columns as those other column will be used in other parts of a larger SQL. Not sure i understand the Return Types explanation. Should i use another approach? — jest west, Sep 19 '18 at 23:07
What is your expected output for dates? The error message clearly says that you cannot use STDEVP on dates. What would be the char value of a date? 2018? — Salman A, Sep 19 '18 at 23:09
Hi Salman, I expect the case statement to evaluate to 0 for dates as the ISNUMERIC check would fail. — jest west, Sep 19 '18 at 23:28

David Browne - Microsoft · Accepted Answer · 2018-09-20T11:51:50.203

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]

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

1 Answers1