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

Question

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

User · Answer

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 gt   myDate and salesDate lt 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  gt   and  lt   you can do it within one single statement  but it s just that the value is used twice

User · Answer

select       from sales  where    dateadd dd  datediff dd  0  salesDate   0     11 11 2010

User · Answer

Something like this   select       from sales  where salesDate  gt    11 11 2010     AND salesDate  lt   Convert datetime   11 11 2010     1

User · Answer

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     lt   7

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2005