CODEWITHSUNDEEP
sql-server
naspinski
naspinski

Reputation: 34707

How to do a SQL NOT NULL with a DateTime?

How does one handle a DateTime with a NOT NULL?

I want to do something like this:

SELECT * FROM someTable WHERE thisDateTime IS NOT NULL

But how?

Upvotes: 19

Views: 176039

Answers (4)

Jose Manuel Jimenez Piqueras
Jose Manuel Jimenez Piqueras

Reputation: 1

SELECT * FROM Table where codtable not in (Select codtable from Table where fecha is null)

Upvotes: -2

H.M.
H.M.

Reputation: 612

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

Upvotes: 2

Mike Dimmick
Mike Dimmick

Reputation: 9802

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.

Upvotes: 2

Johnno Nolan
Johnno Nolan

Reputation: 29657

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

Upvotes: 27

Related Questions