DateTimePicker doesn't filter the datetime values

[skran]

I try to filter my datagridview with a datetimepicker. When the datetime value is just date (eg.15/5/2010) everything work fine. But when there is also date and time value occurs conversion problem "conversion failed when converting date and/or time from character string". Iused also combobox populated with the dattime values but i confronted the same problem.. My goal is to calculate the average readings of a meter. Every next reading is equal to the previous one plus the last period consumption (energy meter). First I calculate the difference between the two readings (which correspond to the dates user choose from the combobox). Next I find the number of readings which are between the two dates.That s my code:

   Dim Table As New DataTable
            Table.Clear()
            Dim sqlCmd As New SqlCommand("SET DATEFORMAT DMY;  select a1.AMR_Reading-a2.AMR_Reading from T_AMR_WaterMeterReadings a1,T_AMR_WaterMeterReadings a2 where a1.AMR_SerialNumber=a2.AMR_SerialNumber and a1.AMR_SerialNumber='" & txtSearchAMRaverage.Text & "' and a1.AMR_DateTime='" & cboDates2.Text & "' and a2.AMR_DateTime='" & cboDates1.Text & "'", conn)
            'dtpToAverage.Value.Date 
            'dtpFromAverage.Value.Date
            'cast(convert(varchar(19),a1.AMR_DateTime)as datetime)
            conn.Close()
            Dim diff As String
            Try
                conn.Open()
                diff = sqlCmd.ExecuteScalar()
                MsgBox(diff)
            Catch ex As SqlException
                MessageBox.Show(ex.Message)
            Finally
            End Try
            Dim dateStart As DateTime
            Dim dateEnd As DateTime
            ' Convert.ToDateTime('" & cboDates1.Text & "').ToString(dd-MM-yyyy HH:mm tt) and Convert.ToDateTime('" & cboDates2.Text & "').ToString(dd-MM-yyyy HH:mm tt)
            dateStart = Convert.ToDateTime(cboDates1.Text).ToString("dd-MM-yyyy HH:mm tt")
            dateEnd = Convert.ToDateTime(cboDates2.Text).ToString("dd-MM-yyyy HH:mm tt")
            Dim sqlCmd1 As New SqlCommand("SET DATEFORMAT DMY; select distinct count(*) from T_AMR_WaterMeterReadings  where  AMR_SerialNumber='" & txtSearchAMRaverage.Text & "'  and AMR_DateTime BETWEEN '" & dateStart & "' and '" & dateEnd & "'", conn)
            'Convert.ToDateTime(AMR_DateTime).ToString(dd-MM-yyyy HH:mm tt)
            conn.Close()
            Dim count As String
            Try
                conn.Open()
                count = sqlCmd1.ExecuteScalar()
                MsgBox(count)

                If count IsNot Nothing Then
                    ' Dim avg As String = sum.ToString()
                    'MsgBox("Η μέση κατανάλωση για το AMR με Serial Number=" & txtSearchAMRaverage.Text & " για το διάστημα από " & dtpFromAverage.Value.Date & " ως " & dtpToAverage.Value.Date & " ισούται με " & avg & ".", vbInformation, "Mέση Κατανάλωση")
                    '  MessageBox.Show(avg)
                    ' MsgBox(result)
                End If
            Catch ex As SqlException
                MessageBox.Show(ex.Message)
            Finally
            End Try

PLEASE help!

[Begginnerdev]

If you are not wanting to use the time, you can simply set the dtp's format to ShortDate and it will remove the time.

If you are wanting the time, you will need to do some error checking to make sure the format will match that of the database.

[G_Waddell]

Date formatting issues can be a real pain... if your database and computer have different date formats it can lead to all kinds of interesting stuff, dd/mm/yyyy vs mm/dd/yyyy even then you can still get different interpretations

I always try to format any date or date time value into a format where there can be no mistake to its value either and pass in as a string - The Database can then handle the format conversion, I also try to output in one of these formats from the database too:

• dd-MMM-yyyy HH:mm:ss (e.g. "31-JUL-2012 16:29:31")
• yyyy-MM-dd HH:mm:ss (e.g. "2012-07-31 16:29:31")