SQL Date Search without time

I have a query that searches by date. the dates in the database include the time. How do I search on just the date only.

select * from weblogs.dbo.vwlogs where Log_time between @BeginDate and @EndDAte and (client_user=@UserName or @UserName Is null) order by Log_time desc

cmd.Parameters.AddWithValue("@BeginDate", txtBeginDate.Text); cmd.Parameters.AddWithValue("@EndDAte", txtEndDate.Text);

From stackoverflow

• Leave your sql mostly as is and just fix your parameters:

  cmd.Parameters.Add("@BeginDate", SqlDbType.DateTime).Value =
      DateTime.Parse(txtBeginDate.Text).Date;       
  cmd.Parameters.Add("@EndDAte", SqlDbType.DateTime).Value =
      // add one to make search inclusive
      DateTime.Parse(txtEndDate.Text).Date.AddDays(1);
  

  You also want to check to make sure your textboxes are valid datetimes first, but you should get the idea.

  The only caveat here is that due to a quirk with the BETWEEN operator it will match the first instant of the next day. So, to fix that we write the query like this:

  SELECT * 
  FROM vwlogs 
  WHERE Log_time >= @BeginDate AND Log_Time < @EndDate 
      AND (client_user=@UserName OR @UserName IS NULL) 
  ORDER BY Log_time DESC
  

  Pay special attention to the comparision operators around the date.

  Joel Coehoorn : Oops: it's just SqlDbType, no 's'. That's what I get for typing directly into a reply window rather than going through Visual Studio first. Fixed the post.

  Joel Coehoorn : As an aside, you should always use an explicit type: letting .Net infer your intended parameter type can lead to hard-to-find performance issues.

• If you want to change the sql instead,

  TRUNC(Log_Time) will reduce every datetime to to that date at midnight.

  Make sure that you build your index on the column as TRUNC(Log_TIME) so it's usable.

• In SQL round the start and end date to Whole Dates and use >= @BeginDate and very specifically < @EndDAte. The "rounding" process is not very elegant I'm afraid

  e.g.

  SELECT @BeginDate = DATEADD(Day, DATEDIFF(Day, 0, @BeginDate), 0),
         @EndDAte = DATEADD(Day, DATEDIFF(Day, 0, @EndDAte) + 1, 0)
  
  select * 
  from weblogs.dbo.vwlogs 
  where     Log_time >= @BeginDate 
        and Log_time < @EndDAte
        and (@UserName Is null OR client_user=@UserName)
  order by Log_time desc
  

  Note that I've moved "@UserName Is null" first, as there is some evidence that this test will easily pass/fail, and will cause the second more CPU intensive test (client_user=@UserName) to be ignored if the first test is TRUE (may be TommyRot of course ...)

  Also, for best performance, you should explicitly name all the columns you need, and not use "SELECT *" (but that may just have been for the purpose of this question)

  Joel Coehoorn : Order of the @username parameters doesn't matter: the optimizer should figure it out and reorder if needed.

  Portman : Technically, this is "flooring" the date, not "rounding it". http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server

  Kristen : Thanks. I meant to set "@EndDAte" to Midnight FOLLOWING, not preceding. I've edited my post

• Another gotcha - truncating your end date will NOT include that date! Consider:

  WHERE Log_Time >= @BeginDate AND Log_Time < @EndDate

  If @EndDate is truncated it will be midnight and not match anything on that day. You'll need to add a day!

• The first thing to do is to remove the times from the dates. If you want to do this in the sql server code you can use something like the code below. I have this as a function on all the databases I work on

  cast(floor(cast(@fromdate as float)) as datetime)
  

  The next thing to worry about is the where criteria. You need to make sure you select everything from the start of the from date to the end of the to date. You also need to make sure queries for one day will work which you can do with a date add like this

  Where LogTime >= @fromdate and LogTime < DateAdd(dd, 1, @todate)
  

• Clean up the dates by adding the following line before your query...

  select 
      @begindate=dateadd(day,datediff(day,0,@begindate),0),
      @enddate=dateadd(ms,-3,dateadd(day,datediff(day,0,@enddate),1))
  

  This will floor your begin date to the lowest possible time (00:00:00.000), and ceiling your end date to the highest possible (23:59:59.997). You can then keep your BETWEEN query exactly as it was written.

  select * 
  from weblogs.dbo.vwlogs 
  where Log_time between @BeginDate and @EndDAte 
  and (client_user=@UserName or @UserName Is null) 
  order by Log_time desc
  

  Hope this helps.