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

  • DTS Transformation vs Select/Insert into t
  • Insert into replicated table fails - identity range check
  • RESTORE HEADERONLY Error 3013 after the sp1 was applied to SQL Server 2014
  • How to convert multiple values in the row with comma separator into multiple columns?
  • How to view transaction logs in SQL Server 2008
  • Is it possible to get the mac address using a stored procedure?
  • 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.