0
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?

AS91
  • 527
  • 7
  • 18

1 Answers1

2

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 ...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • So I'll have to cast or convert all the date times to int to make this work? Will that not affect the date functions and comparisons? – AS91 Aug 16 '16 at 06:02
  • @AmulyaSharma: why are you returning a `smalldatetime` in one case, and an `int` in another?? Just make sure you return **the same datatype** for all different possible return values – marc_s Aug 16 '16 at 06:32