SELECT between two dates


I am trying to do a SELECT that brings me all the records that are between two dates. However, I want to ignore the time (hours, minutes, seconds, etc ...). For example, I have a record that has 2016-11-14 11:47:56.207 , but I send by parameter to my stored procedure only 14-11-2016 . If I send it like that, I do not get results.

WHERE GRP.GRP_Fecha BETWEEN @desde AND @hasta 
asked by sioesi 15.11.2016 в 16:43

3 answers


In your case, I suggest you avoid any solution that involves making a convert or another type of conversion with the field GRP_Fecha . By doing that, you do not allow SQL Server to use an index if there is one for the GRP_Fecha field and this can affect your performance very obviously.

And, of course, between is not ideal in this specific case for the reasons already mentioned in your question.

I suggest the following condition for the best performance (good use of the indexes) and to avoid problems with the hours:

where GRP.GRP_Fecha >= @desde
  and GRP.GRP_Fecha < dateadd(day, 1, @hasta) -- @hasta + 1 día

In the case where:

  • @desde = 2016-11-01
  • @hasta = 2016-11-14

... the condition equals:

where GRP.GRP_Fecha >= '2016-11-01'
  and GRP.GRP_Fecha < '2016-11-15' -- esta es la parte clave

By requesting dates before 2016-11-15 , this includes all 2016-11-14 dates regardless of the time.

answered by 15.11.2016 / 21:38

I solved the problem with the convert function. For your case it would be

WHERE convert(date, GRP.GRP_Fecha) BETWEEN @desde AND @hasta
answered by 15.11.2016 в 16:47

This will work even if it is in Year / month / day format: Time: Min: Sec

 select * from table where Date >= '2016-10-01' and Date <= '2016-11-01' 
answered by 21.11.2016 в 23:40