Odd null behavior

I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.

Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)

create table #t2
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)

select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField

drop table #t1
drop table #t2

The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?

Thanks in advance.

straight out of BOL:

"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."

use the IS NULL clause to assist.

HTH,

Derek

|||Then why does this return a record?:

set ANSI_NULLS off
go
declare @.var datetime
select @.var = null

create table #t1
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')

select * from #t1 where #t1.DateField <> @.var

drop table #t1|||

ANSI NULLS setting only affects comparisons of the form:

column <Operator> @.value

column <Operator> literal

value <Operator> @.value

value <Operator> literal

and combinations thereof. The column to column comparison will always follow the ANSI semantics for NULL value comparison. So you could write your SELECT like:

-- Depends on whether you have datetime values with the default value or not

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> coalesce(#t2.DateField, '')

--or

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> #t2.DateField

or (#t1.DateField IS NULL and #t2.DateField IS NOT NULL)

or (#t1.DateField IS NOT NULL and #t2.DateField IS NULL)