Menu
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.
431
This way, in an aggregate, you should be able to test against a Date field to check if it is NULL. EDIT: Use the literal #1900-01-01# or NullDate() for Date fields, and the literal #1900-01-01 00:00:00# for DateTime fields. NullDate() will work for Date fields, but not for DateTime fields.
A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0 , which in SQL Server is implicitly an integer representing the number of days since 1900-01-01 .
DateTime is a Value Type like int, double etc. so there is no way to assigned a null value.
So basically when we use != or NOT IN in query, it ignores the records with NULL values for fields. The above queries will consider records with state having a NOT NULL value, but state having a NULL value will not be considered.
SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
