How to select rows for a specific date, ignoring time in SQL Server
SqlSql Server-2005Sql Problem Overview
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!
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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
Solution 3 - Sql
select * from sales where salesDate between '11/11/2010' and '12/11/2010' --if using dd/mm/yyyy
The more correct way to do it:
DECLARE @myDate datetime
SET @myDate = '11/11/2010'
select * from sales where salesDate>=@myDate and salesDate<dateadd(dd,1,@myDate)
If only the date is specified, it means total midnight. If you want to make sure intervals don't overlap, switch the between with a pair of >=
and <
you can do it within one single statement, but it's just that the value is used twice.
Solution 4 - Sql
I know this is an old topic, but I managed to do it in this simple way:
select count(*) from `tablename`
where date(`datecolumn`) = '2021-02-17';
Solution 5 - Sql
Try this:
SELECT * FROM sales WHERE CAST(salesDate AS date) = '2010-10-10'
Solution 6 - Sql
Something like this:
select
*
from sales
where salesDate >= '11/11/2010'
AND salesDate < (Convert(datetime, '11/11/2010') + 1)
Solution 7 - Sql
select
*
from sales
where
dateadd(dd, datediff(dd, 0, salesDate), 0) = '11/11/2010'
Solution 8 - Sql
Try this:
true
select cast(salesDate as date) [date] from sales where salesDate = '2010/11/11'
false
select cast(salesDate as date) [date] from sales where salesDate = '11/11/2010'