vb.net get database data between two date

Question

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?

This will be vulnerable to sql injection attacks. It's practically begging to get hacked. — Joel Coehoorn, Nov 15 '13 at 14:22

Joel Coehoorn · Answer 1 · 2016-01-25T21:43:56.943

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.

laylarenee · Accepted Answer · 2013-11-15T14:39:22.410

1

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];

edited Nov 15 '13 at 14:39

answered Nov 15 '13 at 13:36

laylarenee

3,276
7
32
40

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2013/11/15'' at line 1 – Joseph Kim Nov 15 '13 at 13:47
Fixed! I don't use MYSQL regularly :) – laylarenee Nov 15 '13 at 13:51
To be clear, your SQL contains an error because there is not a "GROUP" clause. If you run my SQL snippet in your MySQL Admin window, it should work. Once you see how it works, you can amend your query in VB.NET. – laylarenee Nov 15 '13 at 13:53
it's work in mySql workbench when i use WHERE [date] BETWEEN '2013/01/01' AND '2013/12/31' but i used datetimepicker to get the date and search the data then it cant work – Joseph Kim Nov 15 '13 at 14:02
Great, half of your problem is fixed (you are now using BETWEEN clause!). Next, you need to figure why your date is not formatted correctly... is the VB variable "startDate" a string or a Date object? – laylarenee Nov 15 '13 at 14:07
Answer amended. Use ToString() to convert a date object to a string: dateObject.ToString("yyyy/MM/dd") – laylarenee Nov 15 '13 at 14:15
"select date, sum(total_price) from bill where Date between '" & startDate.ToString("yyyy/MM/dd") & " and '" & endDate.ToString("yyyy/MM/dd") & "'" this code got error? – Joseph Kim Nov 15 '13 at 14:29
You must specify a "GROUP BY" statement when using Aggregate Functions such as SUM(), this is stated in the original answer. The line of code I posted should work, It was corrected once you stated that startDate was a Date object. – laylarenee Nov 15 '13 at 14:35

score 1 · Answer 3 · answered Oct 11 '15 at 08:15

1

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#"

answered Oct 11 '15 at 08:15

Sinoo Yassine

11
1

score 0 · Answer 4 · edited May 23 '17 at 12:15

0

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?

edited May 23 '17 at 12:15

Community

1
1

answered Nov 15 '13 at 13:27

Matt Wilko

26,994
10
93
143

score 0 · Answer 5 · answered Nov 15 '13 at 14:07

0

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...

answered Nov 15 '13 at 14:07

basuraj kumbhar

104
9

score 0 · Answer 6 · answered Nov 06 '20 at 05:27

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)

vb.net get database data between two date

6 Answers6