How to do a SQL NOT NULL with a DateTime?

erm it does work? I've just tested it?

/****** Object:  Table [dbo].[DateTest]    Script Date: 09/26/2008 10:44:21 ******/
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

Just to rule out a possibility - it doesn't appear to have anything to do with the ANSI_NULLS option, because that controls comparing to NULL with the = and <> operators. IS [NOT] NULL works whether ANSI_NULLS is ON or OFF.

I've also tried this against SQL Server 2005 with isql, because ANSI_NULLS defaults to OFF when using DB-Library.

I faced this problem where the following query doesn't work as expected:

select 1 where getdate()<>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