CODEWITHSUNDEEP
sqlsql-serversql-server-2012casewhere-clause
Jason Wells
Jason Wells

Reputation: 889

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 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

Upvotes: 3

Views: 25648

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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-031. 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)

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

Upvotes: 5

Amit Singh
Amit Singh

Reputation: 8119

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

Upvotes: 5

Devart
Devart

Reputation: 122002

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
        )

Upvotes: 8

Related Questions