what im trying to do is to make a query consider or not a datetime value so i dont have to do this in a stored procedure
if @considerDate =1
    begin
        select * from table 
        where dateCol = @date
    end
else
    begin 
        select * from table
    end
i would like to do somethink like
select * from table
where   dateCol = ( case 
                    when  @date is not null
                    then @date
            )
In an single query
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.
You can use case in a where , but not like that. Case has to return one value per statement.
The WHERE clause can be combined with AND , OR , and NOT operators.
you just need to add END keyword
SELECT * 
FROM    tableName
WHERE   dateCol =   CASE WHEN @considerDate =1
                         THEN @date 
                         ELSE dateCol 
                    END
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With