vb.net 获取两个日期之间的数据库数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20002114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vb.net get database data between two date
提问by Joseph Kim
I have two datetimepicker, startDate is stored datetimepicker1 value and endDate is stored datetimepicker2 value.
我有两个 datetimepicker，startDate 存储的是 datetimepicker1 值，endDate 存储的是 datetimepicker2 值。
I want to to get the data between startDate and endDate from database.
我想从数据库中获取 startDate 和 endDate 之间的数据。
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?
我试过上面的代码，但它不能工作。任何人都可以帮助我吗？
回答by Joel Coehoorn
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.
不需要或不需要格式。
回答by Sinoo Yassine
in data base i have DD/MM/YYYY solution is to make in VB MM/DD/YYYY
在数据库中我有 DD/MM/YYYY 解决方案是在 VB MM/DD/YYYY 中制作
this is a CODE
这是一个代码
oldbc.CommandText = "select * from recette where Date between #10/09/2015# and #10/011/2015#"
oldbc.CommandText = "select * from recette where Date between #10/09/2015# and #10/011/2015#"
回答by laylarenee
Try this, using the SQL BETWEEN operator, which allows you to specify the lower and upper bounds of a range.
试试这个，使用 SQL BETWEEN 运算符，它允许您指定范围的下限和上限。
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":
您还需要应用分组以使用聚合函数“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];
回答by basuraj kumbhar
Dim bSql As String = "select date, sum(total_price) from bill where Date = " & DateTimePicker1.Text & " and Date=" & DateTimePicker1.Text & ""
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...
设置日期时间选择器日期格式。我希望它对你有帮助...
回答by Matt Wilko
Try formatting your dates like this (you will need to use the Value of the DateTimePicker as well):
尝试像这样格式化您的日期（您还需要使用 DateTimePicker 的值）：
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?
更好的选择是使用参数化查询，然后您不必将日期格式化为任何特定格式。此处的更多信息：如何创建参数化 SQL 查询？我为什么要？
