SQL Server 2005 - Check for Null DateTime Value

I'm trying to count the num开发者_如何学编程ber 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.

SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL

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

try this

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

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

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

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