Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select rows for a specific date, ignoring time in SQL Server

Given a table with a datetime column, how do I query for rows where the date matches the value I specify but ignores the time portion?

For example, select * from sales where salesDate = '11/11/2010'

For this query we don't care about the time. Other queries require the time component so we can't store only the date component.

Thanks!

like image 984
DenaliHardtail Avatar asked Nov 11 '10 15:11

DenaliHardtail


2 Answers

You can remove the time component when comparing:

SELECT *  FROM sales  WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010' 

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT *  FROM sales  -- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999' WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999' 
like image 113
Oded Avatar answered Sep 25 '22 12:09

Oded


I know it's been a while on this question, but I was just looking for the same answer and found this seems to be the simplest solution:

select * from sales where datediff(dd, salesDate, '20101111') = 0 

I actually use it more to find things within the last day or two, so my version looks like this:

select * from sales where datediff(dd, salesDate, getdate()) = 0 

And by changing the 0 for today to a 1 I get yesterday's transactions, 2 is the day before that, and so on. And if you want everything for the last week, just change the equals to a less-than-or-equal-to:

select * from sales where datediff(dd, salesDate, getdate()) <= 7 
like image 39
user111995 Avatar answered Sep 25 '22 12:09

user111995