如何从数据库中检索两个特定日期之间的数据 [英] How do I retrieve data between two specific dates from database

问题描述

我希望在特定日期之间检索数据。我已经使用了BETWEEN因为得到它们但是我的问题是我的查询只考虑两个特定日期之间的数据，而不管Month&年。



HERE idate数据类型为TEXT，即时通讯使用ms-access db



有人请帮助我解决这个问题。

提前致谢。



我尝试了什么：

I am looking to retrieve data between to specific dates.I have used BETWEEN cause to get them But My Problem is My Query is considering data between only two specific dates regardless of Month & Year.

HERE idate datatype is "TEXT" and i m using ms-access db

Somebody Please Help Me to come out of this.
Thanks in advance.

What I have tried:

    Try
    Dim ds As New DataSet()
    cn = New OleDbConnection(str)
    cn.Open()
    cmd = New OleDbCommand("select idate,sum(itotalrs)as itotalrs from ordertable where idate between '" + DateTimePicker1.Text + "' and '" + DateTimePicker2.Text + "' group by idate", cn)
    Dim da As New OleDbDataAdapter(cmd)
    cmd = New OleDbCommand("select idate,sum(itotalrs)as itotalrs from ordertable where idate between '" + DateTimePicker1.Text + "' and '" + DateTimePicker2.Text + "' group by idate", cn)
    cmd.ExecuteReader()
    da.Fill(ds, "ordertable")
    DataGridView1.DataSource = ds.Tables("ordertable")
Catch ex As Exception
    MsgBox(ex.Message.ToString)
End Try
cn.Close()

推荐答案

永远不要在基于字符串的变量中存储日期：始终以最合适的数据类型存储数据 - 或者您稍后会给自己带来很大的问题。

比较字符串值时，整个比较取决于两个字符串中遇到的第一个不同字符 - 所以类似日期'01 / 06/2017'将低于'31 -06-1901'之类的日期 - 因为'0'在字符排序顺序中位于'3'之前。



在DATETIME或DATE栏中存储您的日期，比较将开始起作用。



但不要那样做！永远不要连接字符串以形成SQL命令，因为您将数据库保持打开状态可能会损坏或破坏数据库的SQL注入攻击。始终使用参数化查询，并以原生格式传递值。在这种情况下，直接将DateTimePicker.Value作为参数传递，而不是通过字符串传递

Never store dates in string based variables: always store data in the most appropriate datatype - or you give yourself enormous problems later on.
When you compare string values, the whole comparison depends on the first different character encountered in the two strings - so a date like '01/06/2017' will be lower than a date like '31-06-1901' - because '0' comes before '3' in the character sort order.

Store your dates in DATETIME or DATE columns, and comparisons will start to work.

But don't do it like that! Never concatenate strings to form an SQL command as you leave your DB open to SQL Injection attack which can damage or destroy your DB. Always use parameterised queries, and pass values in native format.In this case, pass the DateTimePicker.Value directly as a parameter instead of going via a string

引用：

HERE idate数据类型为TEXT，即时通讯使用ms-access db

HERE idate datatype is "TEXT" and i m using ms-access db

这就是您遇到问题的原因。 DateTime数据类型的发明完全是为了解决这个问题。



建议：永远不要通过串联构建查询，它打开了sql注入的大门。

SQL注入 [ ^ ]

这篇关于如何从数据库中检索两个特定日期之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！