CODEWITHSUNDEEP
sql-server
LilRazi
LilRazi

Reputation: 760

Use IF condition in WHERE clause

I have a date column in my table. I want to get all the records where the number of months compared to today's date is greater than 10, but I want to apply this condition only if my declared parameter has value @parameter='Apply'. Otherwise, I do not want to enforce the date criteria.

My results should be the 1st, 2nd, and 3rd rows when @parameter ='Apply', else if @parameter is something else, then all four rows would be returned.

  GetDate         Val
------------     -----
 1/12/2013         A
 1/12/2012         B
 1/12/2014         C
 1/12/2015         D

The following works as expected, but with lots of redundant code. Is there way to put the if condition in the where clause ?

DECLARE @parameter VARCHAR(10)='Apply'
if(@parameter='Apply')
begin
select * from testTable where
    DATEDIFF(MONTH,GetDate,GETDATE()) >10
end
else
begin
    select * from testTable
end

Upvotes: 2

Views: 224

Answers (1)

DavidG
DavidG

Reputation: 119166

This will do the same thing:

select * 
from testTable 
where (DATEDIFF(MONTH,GetDate,GETDATE()) >10 AND @parameter='Apply')
OR @parameter <> 'Apply'

Upvotes: 3

Related Questions