Arithmetic overflow error for smalldatetime

Question

COUNT(DISTINCT CASE WHEN DATEDIFF(d, ClientVisit.rev_timeout, ClientVisit.signature_datetime) = 3  THEN
   CASE WHEN ClientVisit.multiple_flag = 1 
        THEN ClientVisit.rev_timein 
        ELSE ClientVisit.clientvisit_id END 
      END

Datatypes

• rev_timeout - smalldatetime
• rev_timein - smalldatetime
• signature_datetime - datetime
• multiple_flag - bit
• clientvisit_id - int.

Error

Arithmetic overflow error converting expression to data type smalldatetime

SQL Server is converting clientvisit_id to smalldatetime and I'm not sure why since I don't see a comparison with another datetime here. Just a count increment when the DATEDIFF returns a 3. Could someone explain why this is happening and a solution?

Answer 1

CASE in T-SQL is an expression that ultimately returns a single, atomic value. Therefore, all parts of a CASE expression should return the same datatype.

This is not the case here! The inner CASE returns

THEN ClientVisit.rev_timein 

which is of datatype smalldatetime, while the ELSE part:

ELSE ClientVisit.clientvisit_id 

returns a value of datatype int.

T-SQL will try to convert those values according to this data type precedence list and thus tries to convert the int to a smalldatetime and fails.

What to learn: CASE is an expression which should return the same datatype from all it's THEN and ELSE branches ...