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