Your first requirement was to select only based on time without regard to the date. The following example casts each DateTime data type as a Time data type so that only the time value is compared. Solution assumes that DateTimePicker returns a time of the format hh:mm:ss tt or HH:mm:ss.
Dim strSQL As String = "Select col1,col2,col3,InvTime from table1 where CAST(InvTime AS Time) between CAST(@Time1 AS Time) AND CAST(@Time2 AS Time);"
Dim obCommand As SqlCommand = New SqlCommand(strSQL, cn)
Dim obParm As New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@Time1"
obParm.Value = CDate(From_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@Time2"
obParm.Value = CDate(To_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = Nothing
Try
rs = obCommand.ExecuteReader
Catch myException As SqlException
Call ShowSQLException(myException)
End Try
Your second requirement was to use dates from a Date DateTimePicker and times from a Time DateTimePicker. Solution assumes that Date DateTimePicker returns MM/dd/yyyy format and Time DateTimePicker returns a time of the format hh:mm:ss tt or HH:mm:ss.
Dim strSQL As String = "Select col1,col2,col3,InvTime from table1 where InvTime between @DateTime1 AND @DateTime2;"
Dim obCommand As SqlCommand = New SqlCommand(strSQL, cn)
Dim obParm As New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@DateTime1"
obParm.Value = CDate(From_Date_DateTimePicker.Value & " " & From_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = New SqlParameter
obParm.SqlDbType = SqlDbType.DateTime
obParm.Direction = ParameterDirection.Input
obParm.ParameterName = "@DateTime2"
obParm.Value = CDate(To_Date_DateTimePicker.Value & " " & To_Time_DateTimePicker.Value)
obCommand.Parameters.Add(obParm)
obParm = Nothing
Try
rs = obCommand.ExecuteReader
Catch myException As SqlException
Call ShowSQLException(myException)
End Try