CODEWITHSUNDEEP
sql
Simon Linder
Simon Linder

Reputation: 3438

WHERE clause condition checking for NULL

let's say I have a table with columns ID, Date1 and Date2 where Date2can be NULL. I now want to have an SQL statement that ignores Date2 if it is NULL.

So I need something like that:

SELECT *
FROM
  [myTable]
WHERE
  ID = @someId
  AND Date1 <= GETDATE()
  AND Date2 >= GETDATE() IF Date2 IS NOT NULL

So I want to check if Date2 is not NULL and then compare it with the current date. If it is NULL then I just want to ignore it.
Hope my request is clear and understandable.

Cheers
Simon

Upvotes: 1

Views: 433

Answers (5)

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Give this sql a try. 

    SELECT *
    FROM yourtable
    WHERE ID = @someId
    AND Date1 <= GETDATE()
    AND (Date2 IS NULL
    AND Date2 >= GETDATE());

Upvotes: 1

felipe.zkn
felipe.zkn

Reputation: 2060

SELECT *
FROM
  [myTable]
WHERE
  ID = @someId
  AND Date1 <= GETDATE()
  AND Date2 NOT IS NULL
  AND Date2 >= GETDATE();

Upvotes: 0

Dave Sexton
Dave Sexton

Reputation: 11188

You could do it this way:

ISNULL(Date2, GETDATE()) >= GETDATE()

Upvotes: 1

PeteGO
PeteGO

Reputation: 5791

AND (Date2 >= GETDATE() OR Date2 IS NULL)

Upvotes: 2

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

AND Date1 <= GETDATE()
AND (Date2 IS NULL OR Date2 >= GETDATE() )

or 

AND Date1 <= GETDATE()
AND COALESCE(Date2, GETDATE()) >= GETDATE()-- which means : if Date2 IS NULL say Date2 = GETDATE()

Upvotes: 3

Related Questions