T-SQL CASE WHEN BETWEEN not recognising condition

Question

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?

Answer 1

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());