SQL Server 2005 - Check for Null DateTime Value

Question

I'm trying to count the number of records that have a null DateTime value. However, for some reason, my attempts have failed. I have tried the following two queries without any luck:

SELECT COUNT(BirthDate) 
  FROM Person p
 WHERE p.BirthDate IS NULL

and

SELECT COUNT(BirthDate)
  FROM Person p
 WHERE p.BirthDate = NULL

What am I doing wrong? I can see records with a BirthDate of NULL when I query all of the records.

Answer 1

SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL

Answer 2

All answers are correct, but I'll explain why...

COUNT(column) ignores NULLs, COUNT(*) includes NULLs.

So this works...

SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL

Answer 3

try this

SELECT     COUNT(*) 
FROM     Person p
WHERE     p.BirthDate IS NULL

Answer 4

This is happening because you are trying to do a COUNT on NULL. I think that if you check the messages tab, you may have a message there saying NULL values eliminated from aggregate

What you have to change is the field that you are counting

Select Count (1) FROM Person WHERE BirthDate IS NULL

Select Count (*) FROM Person WHERE BirthDate IS NULL

Select Count (1/0) FROM Person WHERE BirthDate IS NULL

Select Count ('duh') FROM Person WHERE BirthDate IS NULL /* some non null string*/

Answer 5

You need to use "IS NULL" not "= NULL"

SELECT
  COUNT('')
FROM
  Person p
WHERE
  BirthDate IS NULL