Comparing a Date to the value in a DateTime field Microsoft SQL

When it comes to working with Date and Time values in Microsoft SQL Server, sometimes its the simple things that get the best of you. Suppose you have a SQL Table that contains the following columns and record:

id      | StartDate               | EndDate 
-----------------------------------------------------------
1       | 2007-06-14 14:22:32.437 | 2010-04-21 16:37:34.503

So you write a query similar to the following query expecting to get that record:

SELECT * FROM TableName 
WHERE StartDate = '6/14/2007' 
AND EndDate = '4/21/2010';

Wrong! If your DateTime value has an actual Timestamp other then "00:00:00.000", your query will NOT return any results. This is probably one of the most common DateTime related mistakes programmers make.

To only compare the DATE portion of the DateTime field value, you could use the following SQL query instead:

SELECT * FROM TableName
WHERE CAST(CONVERT(varchar(8), StartDate, 112) AS datetime) = '6/14/2007' 
AND CAST(CONVERT(varchar(8), EndDate, 112) AS datetime) = '4/21/2010';

So please, don't write queries like the first one ever again!

Pete Soheil
DigiOz Multimedia
www.digioz.com