SQL Date Search without time

Question

I have a query that searches by date. Dates in the database include time. How to search only by date.

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

Answer 1

Leave your sql basically as is and just correct 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 that your text fields are valid at the beginning, but you should get this idea.

The only caveat here is that, due to the quirk with the BETWEEN operator, it will correspond to the first moment of the next day. So, to fix this, we write the query as follows:

 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 particular attention to comparison operators over a date.

Answer 2

In SQL, from the start and end dates to integer dates and use> = @BeginDate and very specifically <@ end date. The rounding process is not very elegant, I'm afraid

eg.

 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 

Please note that I adopted "@UserName Is null", as there is some evidence that this test will easily pass / fail and will ignore the second more intensive CPU test (client_user = @UserName) if the first test is TRUE (maybe , TommyRot, of course ...)

Also, for best performance, you should explicitly specify all the columns you need and not use "SELECT *" (but this could only be for the purposes of this question).

Answer 3

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

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

The next thing to worry about is the criteria. You need to make sure that you have chosen everything from beginning to end. You also need to make sure that one-day requests will work, which you can do with adding a date like this

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

Answer 4

If you want to change sql instead,

TRUNC (Log_Time) will reduce the time of each day to this date at midnight.

Make sure you build your index in the column as TRUNC (Log_TIME) so that it can be used.

Answer 5

Another trick is truncating the end date will not include this date! Consider:

WHERE Log_Time> = @BeginDate AND Log_Time <@EndDate

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

Answer 6

Clear the dates by adding the following line before the query ...

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

This will be the floor start date to the least possible time (00: 00: 00.000) and the ceiling end date to the maximum possible (23:59: 59,997). You can then save the BETWEEN request 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.