"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 tempdbgodeclare @temp_aId int
if OBJECT_ID('temp_a') is not nulldrop table temp_aif OBJECT_ID('temp_b') is not nulldrop table temp_bcreate 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)
insertinto temp_aselect GETDATE(), GETDATE()set @temp_aId = @@IDENTITY
insertinto temp_bselect @temp_aId, GETDATE(), GETDATE()
select *from temp_a ainner join temp_b bon b.temp_aId = a.Idwhere (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_aselect 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 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?