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