Filter by period in vb

0

I created a filter per period 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 in where is datetime . The filter works, but I have a small problem, I'll give you an example:

  • If you put date start = 17-10-2018 and end date = 20-10-2018, in the database data return, returns the data from day 17, as placed on the start date, but only returns the data until the 19th, but I intended it to be until the 20th as I put it on the end date. Returns always the day before in relation to the day I place on the end date. Can anyone help?
asked by anonymous 22.10.2018 / 09:24

1 answer

0

I decided to format the date of the column that comes from the database and the variable @dtInicio , so it was only to change 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 ..."

In this way I already return everything from the day I select in the variable start date until the day that I select in the end date variable.

    
23.10.2018 / 16:25