SQL Server 2012 - Case statement in where clause

Question

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 datePart in the dateDiff function if foo is = 0

SELECT foo,
       bar,
       test
FROM table
WHERE bar = 1,
CASE WHEN foo = 0
        AND dateDiff(dd, getDate(), 2 ) < test
ELSE
        AND dateDiff(hh, getDate(), 2 ) < test
END

I don't know why but this is a common mistake people make in SQL - they introduce a `CASE` expression when really, all they really need is the basic logical operators. — Damien_The_Unbeliever, Jul 03 '13 at 06:37
As in Devart's answer, all you need to say is `(foo = 0 AND DATEDIFF(dd,...) OR foo!=0 AND DATEDIFF(hh,...))` — Damien_The_Unbeliever, Jul 03 '13 at 06:42
Although now I come to look at it, I'm fairly certain, whatever you were aiming for, those `DATEDIFF` calls aren't doing what you think they are. Hint: `DATEDIFF` returns an integer, which (if being compared with a `datetime`) will be implicitly converted to a `datetime` as a number of days since `1900-01-01`. And the third parameter to `DATEDIFF` is meant to be a date, `2` will be implicitly converted to `1900-01-03` — Damien_The_Unbeliever, Jul 03 '13 at 06:45

score 8 · Accepted Answer · answered Jul 03 '13 at 06:35

8

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
        )

answered Jul 03 '13 at 06:35

Devart

119,203
23
166
186

Not following this.. Where is the case to say only use DD if foo = 0 – Jason Wells Jul 03 '13 at 06:39
3

This is the equal expression. Why you need `CASE`? – Devart Jul 03 '13 at 06:42

score 5 · Answer 2 · answered Jul 03 '13 at 06:36

5

You can try like this

SELECT foo,
       bar,
       test
FROM table
WHERE bar = 1 And
(CASE WHEN foo = 0
    then   dateDiff(dd, getDate(), 2 )
ELSE
       dateDiff(hh, getDate(), 2 ) 
END)<test

answered Jul 03 '13 at 06:36

Amit Singh

8,039
20
29

1

`Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'AND'.` – Devart Jul 03 '13 at 06:37

Damien_The_Unbeliever · Answer 3 · 2013-07-03T07:08:15.737

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
FROM table
WHERE bar = 1 AND
(
   (foo = 0 AND DATEADD(day,-2, GETDATE()) < test)
   OR
   (foo<>0 AND DATEADD(hour,-2,GETDATE()) < test)
)

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

select CAST(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.

Sorry Damien, my initial example was wrong. I am comparing 2 dates and not an int as the 3rd param. Thanks for the thorough follow up though! — Jason Wells, Jul 03 '13 at 07:08

SQL Server 2012 - Case statement in where clause

3 Answers3

Linked