CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
user70192
user70192

Reputation: 14204

SQL Server 2005 - Check for Null DateTime Value

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.

Upvotes: 15

Views: 102910

Answers (5)

Raj More
Raj More

Reputation: 48016

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

Upvotes: 4

gbn
gbn

Reputation: 432200

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

Upvotes: 17

MattB
MattB

Reputation: 1104

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

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

Upvotes: 1

Fredou
Fredou

Reputation: 20100

try this

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

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL

Upvotes: 29

Related Questions