SQL Date Search without time

asp.netsql-server

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);

Best Solution

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.