Home / Questions / 33895
Accepted
sioesi
Asked: 2020-11-16 07:43:32 +0800 CST 2020-11-16 07:43:32 +0800 CST

SELECT between two dates

  • 772

I am trying to make one SELECTthat 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 procedureonly 14-11-2016. If I send it like this, I get no result.

WHERE GRP.GRP_Fecha BETWEEN @desde AND @hasta
sql

3 Answers

  1. Best Answer

    In your case, I suggest you avoid any solution that involves doing one converttype of conversion or another with the field GRP_Fecha. By doing so, you do not allow SQL Server to be able to use an index if there is one for the field GRP_Fechaand this can affect your performance in very obvious ways.

    And of course betweenit's 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 is equal to:

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

    When asking for dates before 2016-11-15, this includes all dates of 2016-11-14regardless of time.

    • 2

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

    WHERE convert(date, GRP.GRP_Fecha) BETWEEN @desde AND @hasta
    • 1

  3. This will work for you even if it has a Year/month/day format: Hour:Min:Seg

     select * from table where Date >= '2016-10-01' and Date <= '2016-11-01'
    • 0