Wednesday, September 29, 2010

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

into    temp_a
select  GETDATE(), GETDATE()
set @temp_aId = @@IDENTITY

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 )
Is the expression to be checked for NULL. check_expression can be of any type.
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?

No comments:

Post a Comment