Filter per period in Vb

Question

I created a period filter in Vb with the following code:

Public Sub filtrar()
        Dim dtinicio As DateTime
        Dim dtfim As DateTime
        Dim tabela As New DataTable
        Dim strsql As String

        dtinicio = dtpInicio.Value.ToLongDateString
        dtfim = dtpFim.Value.ToLongDateString

        strsql = "SELECT RegistoEntradaSaida.Movimento, RegistoEntradaSaida.DataHora, Utente.Nome, Relação.Descrição AS Relação, Relacionado.Nome AS Visitante, RegistoEntradaSaida.CartãoID, RegistoEntradaSaida.DataHoraEntrada FROM ((RegistoEntradaSaida INNER JOIN Relação ON RegistoEntradaSaida.RelaçãoID = Relação.ID) INNER JOIN Relacionado ON RegistoEntradaSaida.RelacionadoID = Relacionado.ID) INNER JOIN Utente ON RegistoEntradaSaida.UtenteID = Utente.ID Where RegistoEntradaSaida.DataHora between @dtInicio And @dtFim And Utente.Nome Like @Nome ORDER BY RegistoEntradaSaida.DataHora DESC;"
        Dim cmd As New OleDbCommand
        Try

            CN.Open()
            cmd.Connection = CN
            cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
            cmd.Parameters.AddWithValue("@dtFim", dtfim)
            cmd.Parameters.AddWithValue("@Nome", TextBox1.Text + "%")
            cmd.CommandText = strsql

            Using reader As OleDbDataReader = cmd.ExecuteReader()
                tabela.Load(reader)
                DataGridView1.DataSource = tabela
            End Using

        Catch ex As Exception
            MessageBox.Show("Erro : " & ex.Message, "Consultar Registros")
        Finally
            CN.Close()
        End Try
        DataGridView1.Columns("DataHoraEntrada").DefaultCellStyle.Format = "HH:mm"
    End Sub

The column RegistoEntradaSaida.DataHora that I use on where is datetime. The filter works, but I have a little problem, let me give you an example:

• If you put start date = 17-10-2018 and end date = 20-10-2018, in the return of the data from the database, returns the data from day 17, as placed on the start date, but only return the data until day 19, but I intended it to be until day 20 as put on the end date. Always returns one day earlier in relation to the day I put the end date. Anyone can help?

Answer 1

I decided by formatting the date of the column that comes from the database and the variable @dtInicio, so I just changed the part of where of query as shown below:

strsql = "...Where Format(RegistoEntradaSaida.DataHora, 'dd/mm/yyyy') between Format(@dtInicio, 'dd/mm/yyyy') And @dtFim ..."

This way already returns everything from the day I select in the start date variable until the day I select in the end date variable.