CODEWITHSUNDEEP
sqlsql-server-2005
cmv
cmv

Reputation: 956

SQL check for NULLs in WHERE clause (ternary operator?)

What would the SQL equivalent to this C# statement be?

bool isInPast = (x != null ? x < DateTime.Now() : true)

I need to construct a WHERE clause that checks that x < NOW() only if x IS NOT NULL. x is a datetime that needs to be null sometimes, and not null other times, and I only want the WHERE clause to consider non-null values, and consider null values to be true.

Right now the clause is:

dbo.assignments.[end] < { fn NOW() }

Which works for the non-NULL cases, but NULL values always seem to make the expression evaluate to false. I tried:

dbo.assignments.[end] IS NOT NULL AND dbo.assignments.[end] < { fn NOW() }

And that seems to have no effect.

Upvotes: 2

Views: 6574

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

For use in a WHERE clause, you have to test separately

where dbo.assignments.[end] is null or dbo.assignments.[end] < GetDate()

or you can turn the nulls into a date (that will always be true)

where isnull(dbo.assignments.[end],0) < GetDate()

or you can do the negative test against the bit flag derived from the below

where case when dbo.assignments.[end] < GetDate() then 0 else 1 end = 1

The below is explanation and how you would derive isInPast for a SELECT clause.

bool isInPast = (x != null ? x < DateTime.Now() : true)

A bool can only have one of two results, true or false.
Looking closely at your criteria, the ONLY condition for false is when

x != null && x < now

Given that fact, it becomes an easy translation, given that in SQL, x < now can only be evaluated when x!=null, so only one condition is needed

isInPast = case when dbo.assignments.[end] < { fn NOW() } then 0 else 1 end

(1 being true and 0 being false)

Not sure what { fn NOW() } represents, but if want SQL Server to provide the current time, use either GETDATE() or if you are working with UTC data, use GETUTCDATE()

isInPast = case when dbo.assignments.[end] < GetDate() then 0 else 1 end

Upvotes: 5

Remus Rusanu
Remus Rusanu

Reputation: 294187

Have two separate queries, one when x is null one when is not. Trying to mix the two distinct conditions is the sure shot guaranteed way to get a bad plan. Remember that the generated plan must work for all values of x, so any optimization based on it (a range scan on an index) is no longer possible.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166326

You need something like

WHERE X IS NULL
OR X < NOW()

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55479

The one you are looking for is probably the CASE statement

Upvotes: 0

Related Questions