Reputation: 69
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
Upvotes: 0
Views: 3793
Reputation: 216358
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)
Upvotes: 4