I am running a query to check if any records contain a blank DateOfBirth, but data returned is not what I expected.
I ran the following:
SELECT TOP 5 DateOfBirth, *
FROM [MyDataBase].[dbo].[CustomerRecord]
WHERE DateOfBirth = ''
When I run this, my results show rows like this:
| ID | DateOfBirth | Surname |
|---|---|---|
| 1 | 1900-01-01 | Jones |
| 2 | 1900-01-01 | Deacon |
| 6 | 1900-01-01 | Bacon |
| 10 | 1900-01-01 | James |
| 12 | 1900-01-01 | Burns |
The Information_SCHEMA.Columns shows that the column is defined as date and the COLUMN_DEFAULT is NULL and IS_NULLABLE is set to YES.
So why does it not return rows that are blank?
Answer
You say that DateOfBirth is a DATE. So why are you comparing it to a string at all? You shouldn't. Compare dates with dates, strings with strings etc. Don't mix the types. A date is never equal to an empty string (''), because these are two different things. The results you are getting are hence somewhat arbitrary. Obviously, SQL Server takes the liberty to interprete the empty string as equal to the date 1900-01-01, which it shouldn't in my opinion.
A "blank" DateOfBirth would be a date that is NULL. Hence:
SELECT TOP 5 DateOfBirth, *
FROM [MyDataBase].[dbo].[CustomerRecord]
WHERE DateOfBirth IS NULL;