CODEWITHSUNDEEP
sql-servert-sqldatetimedateadddate-comparison
ARNDG2
ARNDG2

Reputation: 123

T-SQL CASE WHEN BETWEEN not recognising condition

I have a query that is trying to determine whether dates in a table are currently between now and into a day in the future, for a certain ID.

SELECT
    COUNT(CASE
            WHEN MyDate >= GETDATE() AND MyDate < DATEADD(DAY, 1, GETDATE()) 
               THEN 1
               ELSE 0
          END) AS DatesNearlyOverdue
FROM MyTable
WHERE MyId = 1;

My issue is that the WHEN condition seems to be ignored, and will return the same results as WHEN TaskDateDue < GETDATE() THEN 1, for example. I have also tried conditions using DATEDIFF and BETWEEN but the results are always the same.

What is causing all of these conditions to return the same results?

Upvotes: 1

Views: 190

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

If you really want to use THEN 1 ELSE 0 branches, because it makes your code easier to read, you are actually looking for SUM():

SELECT
    SUM (
        CASE
            WHEN MyDate >= GETDATE() AND MyDate < DATEADD(DAY, 1, GETDATE()) 
                THEN 1
            ELSE 0
        END) AS DatesNearlyOverdue
FROM MyTable
WHERE MyId = 1;

Upvotes: 1

T.J. Crowder
T.J. Crowder

Reputation: 1075755

You're using COUNT, which is just as happy to count 0s as 1s. If you want your ELSE values ignored by COUNT, use NULL rather than 0:

SELECT
    COUNT(
        CASE
            WHEN MyDate >= GETDATE() AND MyDate < DATEADD(DAY, 1, GETDATE()) THEN 1
            ELSE NULL -- <== Change is here
            END) AS DatesNearlyOverdue
FROM MyTable
WHERE MyId = 1;

Having said that, the more straightforward version of that query would be:

SELECT  COUNT(1) AS DatesNearlyOverdue
FROM    MyTable
WHERE   MyId = 1
AND     MyDate >= GETDATE()
AND     MyDate < DATEADD(DAY, 1, GETDATE());

Upvotes: 4

Related Questions