CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jcvegan
jcvegan

Reputation: 3170

Sql conditional statement on where clause

I want to make a query using a conditional to select a clause for filter. Something like this.

SELECT Id,name,last_name,submitDate,lastvisitDate 
from Visitor Where if(submitDate is not null) begin submitDate >= @dateFrom end
and if(submitDate is not null) begin submitDate < @dateTo end

but I need for example if @dateTo is null this variable doesn't participate on the filter query

Upvotes: 1

Views: 194

Answers (4)

Adam Porad
Adam Porad

Reputation: 14471

I've updated the WHERE clause of your example query, so that records will be included in the results if submitDate is null or if submitDate is between @dateFrom and @dateTo

SELECT Id, name, last_name, submitDate, lastvisitDate 
FROM Visitor 
WHERE (submitDate IS NULL) OR ((submitDate >= @dateFrom) AND (submitDate < @dateTo))

Upvotes: 0

Andriy M
Andriy M

Reputation: 77737

You can apply ISNULL to @dateTo and thus default it to some really distant future date, like 30000101:

…
WHERE submitDate IS NULL
   OR submitDate >= @dateFrom AND submitDate < ISNULL(@dateTo, '30000101')

Upvotes: 1

Dan Suceava
Dan Suceava

Reputation: 157

where (submitDate is null or (submitDate >= @dateFrom and submitDate < @dateTo))

Upvotes: 2

STO
STO

Reputation: 10658

select * from Visitor where (submitDate is null or submitDate >= @dateFrom) and (submitDate is null or submitDate < @dateTo)

Upvotes: 1

Related Questions