ISNULL and Operand type clash: int is incompatible with date.

So, just ran into this problem. After running a query with some checks in it i got this strange error

"Operand type clash: int is incompatible with date". Strange? Not really! An integer isn't compatible with date. But why? And why do i not get the same error when i use a datetime in stead of the date?

I analysed my problem and came with this :




use tempdb

go

declare @temp_aId int

if OBJECT_ID('temp_a') is not null

  drop table temp_a

if OBJECT_ID('temp_b') is not null

  drop table temp_b 

 

create table temp_a (

  Id int identity(1, 1) not null primary key

, ColDate date

, ColDateTime datetime

)

create table temp_b (

  Id int identity(1, 1) not null primary key

, temp_aId int not null references temp_a(Id)

, ColDate date

, ColDateTime datetime

)

insert

into    temp_a

select  GETDATE(), GETDATE()

set @temp_aId = @@IDENTITY

insert

into    temp_b

select  @temp_aId, GETDATE(), GETDATE()

select  *

from    temp_a a

        inner join temp_b b

          on  b.temp_aId = a.Id

where  (case when isnull(a.ColDate, -1) = isnull(b.ColDate, -1) 

             then 1 else 0 end *

        case when isnull(a.ColDateTime, -1) = isnull(b.ColDateTime, -1) 

             then 1 else 0 end) = 1

select isnull(ColDate, -1) from temp_a

select isnull(ColDateTime, -1) from temp_a

You can run it as it is here and it is pretty straightforward, this is what it does: It creates two tables with a date and a datetime, just for proof of concept ;-) In my original query i join these tables and perform a check if the 2 tables are equal. I do this with the "case when isnull(a.column, -1) = isnull(b.column, -1) then 1 else 0 end" statement. If a column isn't equal then a zero is returned, and we all know that "0 = 1" is false! So if a column isn't equal it should return false and start to insert things in the original query.

But all i got was the "Operand type clash: int is incompatible with date" error.

It looks like it is the ISNULL function. When i look into BOL (Books OnLine) i see this :

"

ISNULL ( check_expression , replacement_value )

check_expression

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

Ok. So the check_expression can be of any type, and the replacement_value has to be of a type that is convertible to the check_expression type. Makes sense.According to BOL a conversion between INT  and DATE is not allowed, and only an explicit conversion is allowed between an INT and a DATETIME.

Why does it say IMPLICITLY? I don't get it. It can only do implicit conversions, but a DATETIME to -1 is allowed, but according to BOL it is a EXPLICIT conversion.

Then why isn't a convert from DATE to -1 not valid?

Anyone has a clue?