SQL Server – using CASE in WHERE clause

I want to use CASE in WHERE clause, but it returns error.

Can I use CASE in where clause? or how to fix it ? Thanks

  • How can I write an Excel plugin that loads a SQL Server data table?
  • Cannot import SQL Azure bacpac to 2016 CTP
  • SQL - Replacing an is null integer with a varchar
  • SqlDependency OnChange Not Firing
  • UPDATE Stored Procedure not Updating
  • How to assign default value for DBNull values from DataTable when performing bulk insert/update as IEnumerable?
  • SELECT * FROM hris_leave.dbo.tbl_act 
    inner join hris_leave.dbo.tstaff on hris_leave.dbo.tstaff.s_id =        hris_leave.dbo.tbl_act.s_id 
    where 
     case when acting_to is not null 
    then 
    datediff(day, acting_from, acting_to) >= 90 and acting_to >= '2010-10-01' 
    else
    acting_to is null
    order by  hris_leave.dbo.tbl_act.s_id 
    

    One Solution collect form web for “SQL Server – using CASE in WHERE clause”

    In your case, you only need OR

    WHERE
        (
        acting_to is null 
        OR 
            (
            datediff(day, acting_from, acting_to) >= 90
            AND
            acting_to >= '2010-10-01'
            )
        )
    

    A case is for values, not conditions. The condition is outside the CASE expression

    eg

    CASE
        WHEN SomeCol = 'a' THEN ColA
        WHEN SomeCol = 'c' THEN ColC
        ELSE ColB
    END > 42
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.