How to Search for Date and Time Values Using SQL Server 2000
Before you can effectively query date/time (or temporal) data, you have to know something about how date/time values are stored. In this article, Bryan demonstrates the various ways in which to query an SQL Server to successfully retrieve the records you may be seeking.
How to Search for Date and Time Values Using SQL Server 2000 - Dates Without Times and Times Without Dates (Page 4 of 8 )
SQL Server doesn’t provide data types for storing just the date or just the time. So if you store a date/time value without an explicit time, the fractional portion of the value is set to zero. This represents midnight as 00:00:00. Similarly, if you store a date/time value without an explicit date, the integer portion of the value is set to zero. This represents the date January 1, 1900. To see this, submit the following query:
SELECT CAST('1900-01-04' AS datetime), CAST('10:00' AS datetime)
which returns the following result:
1900-01-04 00:00:00.000 1900-01-01 10:00:00.000
Whether you can ignore the date or the time component when you query a date/time column depends on how the column has been designed and used.