CODEWITHSUNDEEP
mysqldatabasevb.netdatetimepicker
Joseph Kim
Joseph Kim

Reputation: 71

vb.net get database data between two date

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?

Upvotes: 0

Views: 20650

Answers (6)

Ray E
Ray E

Reputation: 154

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)

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

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.

Upvotes: 3

Sinoo Yassine
Sinoo Yassine

Reputation: 11

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

Upvotes: 1

laylarenee
laylarenee

Reputation: 3284

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

Upvotes: 2

basuraj kumbhar
basuraj kumbhar

Reputation: 104

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

Upvotes: 0

Matt Wilko
Matt Wilko

Reputation: 27322

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?

Upvotes: 0

Related Questions