'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?

sql-server


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mike Dimmick
Solution 2 H.M.
Solution 3 Jose Manuel Jimenez Piqueras

Related Questions