Compare a date string to datetime in SQL Server?

Solution 1:

Technique 1:

 DECLARE @p_date DATETIME
 SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

 SELECT  *
 FROM    table1
 WHERE   column_datetime >= @p_date
 AND     column_datetime < DATEADD(d, 1, @p_date)

The advantage of this is that it will use any index on 'column_datetime' if it exists.

Solution 2:

In SQL Server 2008, you could use the new DATE datatype

DECLARE @pDate DATE='2008-08-14'  

SELECT colA, colB
FROM table1
WHERE convert(date, colDateTime) = @pDate  

@Guy. I think you will find that this solution scales just fine. Have a look at the query execution plan of your original query.

And for mine: