Date range in vb.net

I have following code and I can filter data on grid but when I pick same date in 2 datepicker it shows nothing. How can I fix the problem. Any help will be appreciated.

con = New SqlCeConnection(constring)
    con.Open()
    cmd = New SqlCeCommand("SELECT * FROM tblMeter WHERE (Date >= @startDate) AND (Date <  @endDate)", con)
    Dim param1, param2 As SqlCeParameter
    param1 = New SqlCeParameter("@startDate", DateTimePicker1.Value)
    param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value)
    cmd.Paramet开发者_JAVA百科ers.Add(param1)
    cmd.Parameters.Add(param2)
    Dim da As New SqlCeDataAdapter(cmd)
    Dim dt As New DataTable
    da.Fill(dt)
    con.Close()
    DataGridView1.DataSource = dt

Thanks

param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value.AddDays(1))

Remember that Sql Server interprets a date like this: 2010-06-23
as a date like this: 2010-06-23 00:00:00.000.

In other words, even if you use >= and <= to check the range at both ends you're still only giving yourself a one millisecond timespan. Sometimes you get away with that if all the dates in the column have no time component, but it's rarely what you really want. Instead, you need to add one day to the end of your range so that your filter includes the entire day, and not just the first millisecond.

That's because @endDate implies the time 00:00, to include the whole day add the time 23:59:59. Or add 1 to @endDate.

Change your query to less-than or equal-to your end date.

SELECT * FROM tblMeter WHERE (Date >= @startDate) AND (Date <= @endDate)

It's your logic:

If date is 2001 and you input 2001:

2001 >= 2001 - check
2001 < 2001 - Nope

Try changing the exclusive

(Date < @endDate)

to the inclusive

(Date <= @endDate)

You may use:

param1 = New SqlCeParameter("@startDate", DateTimePicker1.Value.ToShortDateString)
param2 = New SqlCeParameter("@endDate", DateTimePicker2.Value.AddMinutes(1))