从数据库中获取数据 WHERE 日期是今天 VB.net

Get data from database WHERE date is today VB.net

提问by Jonas

I want to get all the fields from a row where the date from column "Date" is today's date.

我想从“日期”列中的日期是今天的日期的行中获取所有字段。

My code is

我的代码是

Dim today As Date = DateTime.Now
vandaag = Format(today, "dd/MM/yyyy")

"select * from tblPatients where PatientDate =" & today & ""

Can somebody help me please? It's for school...

有人可以帮我吗？是为了学校...

回答by Steve

Never use string concatenation to build a SQL Command to pass to your database engine.
Using Parameters, you avoid problems in text parsing (dates, string with special characters and so on) but, most important, you avoid Sql Injection Attacks.
This is an habit that every developer that works with database should acquire as fast as possible.
So, supposing you have a OleDbConnection already built and opened, you could write

切勿使用字符串连接来构建 SQL 命令以传递给您的数据库引擎。
使用参数，您可以避免文本解析中的问题（日期、带有特殊字符的字符串等），但最重要的是，您可以避免Sql 注入攻击。
这是每个使用数据库的开发人员都应该尽快养成的习惯。
所以，假设你已经建立并打开了一个 OleDbConnection，你可以写

Dim strSql As String = "select * from tblPatients where PatientDate = ?dt"
Using dadapter = New OleDbDataAdapter()
    dadapter.SelectCommand = New OleDbCommand(strSql, con)
    dadapter.SelectCommand.Parameters.AddWithValue("?dt", DateTime.Today)   
    Dim dset As DataSet = New DataSet()
    dadapter.Fill(dset, "Books")
End Using

Notice also the Using statement, this is another good practice to follow.
Usingwill take care of the disposing of objects like OleDbConnection and OleDbDataAdapter from memory thus releasing all system resources used by the object

还要注意Using 语句，这是另一个要遵循的好习惯。
Using将负责从内存中处理 OleDbConnection 和 OleDbDataAdapter 等对象，从而释放该对象使用的所有系统资源

回答by KekuSemau

Actually, you do not need parameters at all in your query:
SELECT * FROM tblPatients WHERE PatientDate = DATE()
If the PatientDate was a combined date-time, you could use:
SELECT * FROM tblPatients WHERE PatientDate BETWEEN DATE() AND DATEADD('d', 1, DATE())
The Date()-Function will have a time-part as 0:00, so this will give you the right results for the current day.

实际上，您在查询中根本不需要参数：
SELECT * FROM tblPatients WHERE PatientDate = DATE()
如果 PatientDate 是组合的日期时间，您可以使用：
SELECT * FROM tblPatients WHERE PatientDate BETWEEN DATE() AND DATEADD('d', 1, DATE())
Date()-Function 的时间部分为 0:00，因此这将为您提供当天的正确结果。

回答by andy

Dim strSql As String = "select * from tblPatients where PatientDate =#" & today & "#"
Dim dadapter As OleDbDataAdapter
dadapter = New OleDbDataAdapter()
dadapter.SelectCommand = New OleDbCommand(strSql, con)
Dim dset As DataSet = New DataSet()
dadapter.Fill(dset, "Books")

回答by pete

Use the DATEADDand DATEDIFFfunctions to remove the time from the date:

使用DATEADD和DATEDIFF函数从日期中删除时间：

Dim dayPart As String = Chr(34) & "d" & Chr(34) 'Chr(34) = ", resulting string is "d" (enclosed in quotes)
Dim query As String = "SELECT * FROM tblPatients"
'Declared as separate variables for better readability
Dim patientDate As String = "DATEADD(" & dayPart & ",  DATEDIFF(" & dayPart & ", 0, PatientDate), 0)"
Dim todaysDate As String = "DATEADD(" & dayPart & ",  DATEDIFF(" & dayPart & ", 0, Now());"
'patientDate = 'DATEADD("d",  DATEDIFF("d", 0, PatientDate), 0)' which is the patientDate, stripped of a timevalue
'todaysDate = 'DATEADD("d",  DATEDIFF("d", 0, Now()), 0)' which is today's date, stripped of a timevalue
'This works because:
'DATEDIFF("d", 0, PatientDate) returns the number of days that have passed since the "zero" date and strips the time component
'DATEADD("d",  DATEDIFF("d", 0, PatientDate), 0) adds the DATEDIFF calculation to the "zero" date returning a date
'equal to PatientDate with no time component.
'This same principle is applied to the 'Now()' function to get today's date with no time component.
'
'Now that patientDate can equal today's date (because the time components have been stripped away), we can affix the where clause
query &= " WHERE " & patientDate & " = " & todaysDate
'and run the query

回答by Ad Kahn

Try this

尝试这个

"select * from tblPatients where PatientDate =" & Now() & ""

or

或者

 "select * from tblPatients where PatientDate =" & Now().Date() & ""