Compare a date string to datetime in SQL Server?
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.
In SQL Server 2008, you could use the new DATE datatype
DECLARE @pDate DATE='2008-08-14' SELECT colA, colBFROM table1WHERE 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:
Just compare the year, month and day values.
Declare @DateToSearch DateTimeSet @DateToSearch = '14 AUG 2008'SELECT * FROM table1WHERE Year(column_datetime) = Year(@DateToSearch) AND Month(column_datetime) = Month(@DateToSearch) AND Day(column_datetime) = Day(@DateToSearch)