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
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.
I solved the problem with the convert function. For your case it would be
WHERE convert(date, GRP.GRP_Fecha) BETWEEN @desde AND @hasta
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'