CODEWITHSUNDEEP
asp.netsql-server
Billy
Billy

Reputation:

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

Upvotes: 0

Views: 2383

Answers (6)

Kristen
Kristen

Reputation: 4291

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)

Upvotes: 1

Portman
Portman

Reputation: 31975

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.

Upvotes: 0

Martynnw
Martynnw

Reputation: 10895

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)

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415921

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.

Upvotes: 3

n8wrl
n8wrl

Reputation: 19765

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!

Upvotes: 0

Mark Brady
Mark Brady

Reputation:

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.

Upvotes: 0

Related Questions