使用 VB.net/MS Access 查询两个日期之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30160505/
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
Query records between two dates using VB.net/MS Access
提问by Rkmunusamy
i am having table in ms access named as stockitems, table structure is..
我在 ms access 中有一个名为 stockitems 的表，表结构是..
stdate stitems
01-04-2015 Red
02-04-2015 Blue
08-04-2015 Green
01-05-2015 Grey
02-05-2015 Violet
09-05-2015 Purple
04-06-2015 Sky Blue
i am using the below code to select records from that table...
我正在使用以下代码从该表中选择记录...
Dim report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim xs, xs2, cmp As String
xs = date1.Text
xs2 = date2.Text
cmp = salsqkrpt.salcom.Text
Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems where stdate between '" & date1.Text & "'and '" & date2.Text & "'",con)
myDA = New OleDbDataAdapter(cmd1)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "stockitems ")
report.Load(Application.StartupPath & "\stockreport.rpt")
report.SetDataSource(myDataSet.Tables("stockitems "))
stckreport.CrystalReportViewer1.ReportSource = report
here date1 and date2 was masked textbox,mask is 00-00-0000 (DD-MM-YYYY)when excuting this code the report only get from first two strings only that is ddonly,when i assign date1=01-04-2015and date2=03-05-2015the result comes like...
在这里date1 and date2 was masked textbox，掩码是00-00-0000 (DD-MM-YYYY)在执行此代码时，报告仅从前两个字符串中获取，dd仅当我分配date1=01-04-2015并且date2=03-05-2015结果如下...
stdate stitems
01-04-2015 Red
02-04-2015 Blue
01-05-2015 Grey
02-05-2015 Violet
but my expected result is
stdate stitems
01-04-2015 Red
02-04-2015 Blue
08-04-2015 Green
01-05-2015 Grey
02-05-2015 Violet
it does'nt works as a date please help me out of this problem developers and i am beginner to vb.net. thanks in advance.
它不能作为约会对象，请帮助我解决这个问题的开发人员，我是 vb.net 的初学者。提前致谢。
回答by Ciarán
MS/Access interprets dates as mm/dd/yy and so you are selecting data from January 4th -> March 5th. Reformat your dates in the SQL as mm-dd-yyyy. Generally Ms/Access actually requires the date literals in the format #mm/dd/yyyy# (i.e. with the #'s).
MS/Access 将日期解释为 mm/dd/yy，因此您要选择 1 月 4 日 -> 3 月 5 日的数据。将 SQL 中的日期重新格式化为 mm-dd-yyyy。通常，Ms/Access 实际上需要格式为#mm/dd/yyyy#（即带有#）的日期文字。
Dim dtDate1 as DateTime = DateTime.Parse(date1.text)
Dim dtDate2 as DateTime = DateTime.Parse(date2.text)
Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems where stdate Between #" & _
dtDate1.ToString("MM/dd/yyyy") & "# And #" & _
dtDate2.ToString("MM/dd/yyyy") & "#",con)
Your code leaves you open to SQL Injection. Validate the dates first, and then either pass them as parameters or format your SQL using DataTime variables (above).
您的代码让您对 SQL 注入持开放态度。首先验证日期，然后将它们作为参数传递或使用 DataTime 变量（如上）格式化您的 SQL。
回答by Gustav
You need properly formatted date expressions in your SQL:
您需要在 SQL 中正确格式化日期表达式：
Dim xs1 As string
Dim xs2 As string
Dim sql as string
xs1 = Date.Parse(date1.Text).ToString("yyyy'/'MM'/'dd")
xs2 = Date.Parse(date2.Text).ToString("yyyy'/'MM'/'dd")
sql = "select * from stockitems where stdate between #" & xs1 & "# and #" & xs2 & "#"
Dim cmd1 As OleDbCommand = New OleDbCommand(sql, con)
回答by Thomas G
MS Access internal storage format for dates is US default: MM/DD/YYYY If your windows locale date format is DD/MM/YYYY like in Europe, you should always do a format() prior to querying on your dates.
日期的 MS Access 内部存储格式是美国默认值：MM/DD/YYYY 如果您的 Windows 区域设置日期格式是 DD/MM/YYYY，就像在欧洲一样，您应该始终在查询日期之前执行 format()。
As a result, your query should be :
因此，您的查询应该是：
Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems where stdate between #" & format(date1.Text,"MM/DD/YYYY") & "# and #" & format(date2.Text,"MM/DD/YYYY") & "#",con)
or with -separator like you did :
或者像你一样使用-分隔符：
Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems where stdate between #" & format(date1.Text,"MM-DD-YYYY") & "# and #" & format(date2.Text,"MM-DD-YYYY") & "#",con)
回答by user7625651
In MS-ACCESS OR SQL The DataType Of Date Field Should be Date/Time. And Those Format Should be "DD/MM/YYYY" Then we do not need to change in our standard code...
在 MS-ACCESS OR SQL 中，日期字段的数据类型应该是日期/时间。那些格式应该是“DD/MM/YYYY”然后我们不需要在我们的标准代码中改变......
