I have two datetimepicker, startDate is stored datetimepicker1 value and endDate is stored datetimepicker2 value.
I want to to get the data between startDate and endDate from database.
Dim bSql As String = "select date, sum(total_price) from bill where Date = '" & Format(startDate, "yyyy/MM/dd") & " and Date='" & Format(endDate, "yyyy/MM/dd") & "'"
i tried the code above but it can't work. Anyone can help me?
If you're trying to find a string format for a date at all, you've already lost. Try this:
Dim bSql As String = "select date, sum(total_price) from bill where Date >= @startDate and Date < @endDate;"
Using cn As New MySqlConnection("connection string here"), _
cmd As New MySqlCommand(bSql, cn)
cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = startDate
cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = endDate.AddDays(1)
cn.Open()
'...
End Using
No formatting required or wanted.
Try this, using the SQL BETWEEN operator, which allows you to specify the lower and upper bounds of a range.
Dim bSql As String = "select date, sum(total_price) from bill where Date BETWEEN '" & startDate.ToString("yyyy/MM/dd") & "' AND '" & endDate.ToString("yyyy/MM/dd") & "' GROUP BY date;"
You will also need to apply a grouping to use the aggregate function "SUM":
-- find all dates with sales and the total prices on each date
SELECT [date], SUM(total_price) AS [TotalPrice]
FROM bill
WHERE [date] BETWEEN '2013-01-01' AND '2013-12-31' -- use appropriate date format here
GROUP BY [date];
in data base i have DD/MM/YYYY solution is to make in VB MM/DD/YYYY
this is a CODE
oldbc.CommandText = "select * from recette where Date between #10/09/2015# and #10/011/2015#"
Try formatting your dates like this (you will need to use the Value of the DateTimePicker as well):
Format(startDate.Value, "yyyy-MM-dd")
A better option is to use a parameterised query and then you don't have to format the date into any particular format. More info here: How do I create a parameterized SQL query? Why Should I?
Dim bSql As String = "select date, sum(total_price) from bill where Date = " & DateTimePicker1.Text & " and Date=" & DateTimePicker1.Text & ""
Set the datetime picker date format. I hope it will helpful for you...
Another possibility is to make a little function to format DATE variable and return correct date string syntax for MySQL.
Public Function MySQLDate(inDate As Date) As String
Return "'" & inDate.ToString(format:="yyyy'/'MM'/'dd") & "'"
End Function
Now your query is more readable, easier to create, and looks like this:
Dim bSql As String = "select date, sum(total_price) from bill where Date BETWEEN " & MySQLDate(startDate) & " and " & MySQLDate(endDate)
