SELECT WHERE Date = Day

Question

I have a table with a column of type dateTime. I want to do a query that selects all rows that take place on that date. Basically,

SELECT * FROM Table
WHERE [timeStamp] = '02-15-2003'

But that only returns rows where [timeStamp] is '02-15-2003 00:00:00.000', but really I want rows from anytime that day.

Answer 1

If you have indexes, you are going to want something which doesn't prevent the indexes from being used:

SELECT *
FROM Table 
WHERE [timeStamp] >= '20030215'
      AND [timeStamp] < '20030216'

You can do a truncation operation on the [timeStamp] column to get rid of any time part (implementation dependent), but this can potentially hurt the execution plan. Unfortunately, you really have to look at the execution plan to see this, because sometimes the optimizer is clever about some functions and sometimes it isn't.

Answer 2

You should CAST to DATE if you're on SQL 2008.

select * from [Table]
where cast([timeStamp] as date) = '02-15-2003'

Best approach to remove time part of datetime in SQL Server

--- UPDATE ---

The word the commenters should have used back in 2012 to describe why this is not the optimal solution is sargability. Changing the data type in the WHERE clause, while the simplest solution, has implications for index usage that a bounded search would not.