CODEWITHSUNDEEP
sql-server
Venkatvasan
Venkatvasan

Reputation: 491

Achieve If statement in where clause in SQL

I am trying to check a condition in a where clause

Select * from [Sale Master] where 
IF (@IsTime='1')
BEGIN
convert(char(19),Sale_Date,20) between'2016-05-18 10:45:00' and '2016-05-18 10:55:00'
END
ELSE
BEGIN
convert(char(10),Sale_Date,20) between'2016-05-18' and '2016-05-18'
END

what I am actually trying to do is I want to search record based on the @IsTime bit variable.If it is true the it should considers the date along with time otherwise it considers only date alone.I know that IF condition should not be used in Where Clause but I don't have any Idea about using CASE.

Upvotes: 0

Views: 55

Answers (4)

bmsqldev
bmsqldev

Reputation: 2735

IF (@IsTime='1')
BEGIN
     Select * from [Sale Master] 
     where convert(char(19),Sale_Date,20) between'2016-05-18 10:45:00' and '2016-05-18 10:55:00'
END
ELSE
BEGIN
     Select * from [Sale Master]
     where convert(char(10),Sale_Date,20) between'2016-05-18' and '2016-05-18'
END

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try like this, this is somewhat similar to Gordon Linoff post but I removed a condition.

SELECT *
FROM [Sale Master]
WHERE (
        @IsTime = '1'
        AND sale_date BETWEEN '2016-05-18 10:45:00'
            AND '2016-05-18 10:55:00'
        )
    OR (
        Sale_Date BETWEEN '2016-05-18'
            AND '2016-05-18'
        );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You don't need an if() -- nor a case. More importantly, though: there is no reason to convert dates to strings for comparison. So:

Select *
from [Sale Master]
where (@IsTime = '1' and sale_date between '2016-05-18 10:45:00' and '2016-05-18 10:55:00') or
      (@IsTime <> '1' and Sale_Date between '2016-05-18' and '2016-05-18');

Note: if IsTime is a number, then do no use quotes for the comparison. Also, if the value can be NULL, then the second comparison needs to take that into account.

Actually, it can be dangerous to use between with date/times. I prefer direct comparisons:

Select *
from [Sale Master]
where (@IsTime = '1' and sale_date >= '2016-05-18 10:45:00' and sale_date < '2016-05-18 10:55:00') or
      (@IsTime <> '1' and Sale_Date >= '2016-05-18' and Sale_Date < '2016-05-19');

Aaron Bertrand has a good blog post explaining issues with using between for date/times.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

Select * 
from [Sale Master] 
where 
(
   (@IsTime=1) 
   and 
   convert(char(19),Sale_Date,20) between '2016-05-18 10:45:00' and '2016-05-18 10:55:00'
) 
or          
(
   (@IsTime=0) 
   and 
   convert(char(10),Sale_Date,20) between '2016-05-18' and '2016-05-18'
)

Upvotes: 0

Related Questions