How to do a SQL NOT NULL with a DateTime?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DateTest](
    [Date1] [datetime] NULL,
    [Date2] [datetime] NOT NULL
) ON [PRIMARY]

GO
Insert into DateTest (Date1,Date2) VALUES (NULL,'1-Jan-2008')
Insert into DateTest (Date1,Date2) VALUES ('1-Jan-2008','1-Jan-2008')
Go
SELECT * FROM DateTest WHERE Date1 is not NULL
GO
SELECT * FROM DateTest WHERE Date2 is not NULL

-------------------------------------------------------------------------------

we expect it to show 1 because getdate() doesn't return null. I guess it has something to do with SQL failing to cast null as datetime and skipping the row! of course we know we should use IS or IS NOT keywords to compare a variable with null but when comparing two parameters it gets hard to handle the null situation. as a solution you can create your own compare function like the following:

CREATE FUNCTION [dbo].[fnCompareDates]  
(
  @DateTime1 datetime, 
  @DateTime2 datetime  
)
  RETURNS bit 
 AS 
 BEGIN
  if (@DateTime1 is null and @DateTime2 is null) return 1;
  if (@DateTime1 = @DateTime2) return 1; return 0 END

and re writing the query like:
 
select 1 where dbo.fnCompareDates(getdate(),null)=0