Sql Server 2012 - Case Statement In Where Clause

SQL is not my strong suit, but I cannot figure out why this isn't working. I simply want to run a different AND statement based on a value. Specifically, I want to change the dateP

Solution 1:

Try this one -

SELECT foo,
       bar,
       test
FROM [table]
WHERE bar = 1
    AND (
            (
                foo = 0 
                AND 
                DATEDIFF(dd, GETDATE(), 2 ) < test
            )
        OR  
            DATEDIFF(hh, GETDATE(), 2 ) < test
        )

Solution 2:

You can try like this

SELECT foo,
       bar,
       test
FROMtableWHERE bar =1And
(CASEWHEN foo =0then   dateDiff(dd, getDate(), 2 )
ELSE
       dateDiff(hh, getDate(), 2 ) 
END)<test

Solution 3:

If I had to guess at what you're actually aiming for, it's that you wanted to subtract 2 hours or days from GETDATE() for use in your comparison.

Here's what I think you're aiming for:

SELECT foo,
       bar,
       test
FROMtableWHERE bar =1AND
(
   (foo =0AND DATEADD(day,-2, GETDATE()) < test)
   OR
   (foo<>0AND DATEADD(hour,-2,GETDATE()) < test)
)

I can't think that you really intended those DATEDIFF calls. For example, today (3rd July 2013), this expression:

selectCAST(DATEDIFF(dd,getdate(),2) as datetime)

Produces 1786-07-03. I'm assuming that test is a datetime and that the implicit conversion is being performed. Even if it's not, the numerical value of DATEDIFF(dd,getdate(),2) will always be a large negative number (unless or until it's run on a machine where GETDATE() returns a value from before the 20th Century)

---

Weirdly, I think 3rd July is the only day of the year on which the result will have the same month and day number.