Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT WHERE Date = Day

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.

like image 549
Steve Evans Avatar asked Nov 30 '22 02:11

Steve Evans


2 Answers

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.

like image 199
Cade Roux Avatar answered Dec 29 '22 03:12

Cade Roux


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.

like image 37
mattmc3 Avatar answered Dec 29 '22 01:12

mattmc3