How to search between two dates and time from sql in vb,net?

Question

I am trying to create a Crystal Report where I can search between two dates selected by datetimepicker1 and datetimepicker2. MySql database date column has saved datetime like this (12/26/2018 3:28:30 PM). my select statement and parameters are not correct. How can I do this?

Private Function GetData() As billing

    Dim x = Form25.DateTimePicker1.Value.Date.ToString("MM-dd-yyyy")

    Dim y = Form25.DateTimePicker2.Value.Date.ToString("MM-dd-yyyy")

    Dim constr As String = ("server= PANKAJ\SQLEXPRESS; database = pankaj billing software; integrated security=True")

    Using con As New SqlConnection(constr)

        Using cmd As New SqlCommand("select goldrate,date,itemcode,karat,category,item,qty,grossweight,netweight,discountrs,finalamount from allsaledata where date >= @date AND date <= @date", con)
            cmd.Parameters.AddWithValue("@date", x And y)


            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dsCustomers As New billing()
                    sda.Fill(dsCustomers, "DataTable1")
                    Return dsCustomers

                End Using
            End Using
        End Using
    End Using
End Function

Steve · Accepted Answer · 2019-02-05 19:44:02Z

The first thing to do when you execute a query against a column of type Date is not to pass a parameter of type string. This will force the database engine to do its own conversion and you have a very high risk to get a wrong conversion.

So you should pass parameters of type DateTime and you need two parameters because you have two date limits to check

' Do not convert to strings here. Work with dates
Dim x = Form25.DateTimePicker1.Value.Date
Dim y = Form25.DateTimePicker2.Value.Date

Using cmd As New SqlCommand("select goldrate,date,itemcode,karat,
                                    category,item,qty,grossweight,
                                    netweight,discountrs, finalamount 
                             from allsaledata 
                             where date >= @init AND date <= @end", con)

    cmd.Parameters.Add("@init", SqlDbType.Date).Value = x
    cmd.Parameters.Add("@end", SqlDbType.Date).Value = y

Another thing to pay attention is the fact that, if you use a DateTime without the Time part, then your query could exclude the dates for the last day.

For example, if you have an end date like 02/05/2019 00:00:00, then a record with a date and time like 02/05/2019 19:40:00 will not be included in the results because a time of 00:00:000 is midnight and every other time is after midnight.
If you want to avoid this situation then add a day to the ending date, like this

Dim y = Form25.DateTimePicker2.Value.Date.AddDay(1)

Collectives™ on Stack Overflow

How to search between two dates and time from sql in vb,net?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related