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:
... 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.