Get data from database WHERE date is today VB.net

Question

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

Remove those meaningless tags (select-where) and add the correct tag for your database system. Dates are always a bit difficult to handle in different db systems — Steve, Oct 27 '12 at 11:47

score 2 · Answer 1 · edited May 23 '17 at 12:08

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

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.
Using will take care of the disposing of objects like OleDbConnection and OleDbDataAdapter from memory thus releasing all system resources used by the object

KekuSemau · Accepted Answer · 2012-10-27T13:19:07.840

2

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.

edited Oct 27 '12 at 13:19

answered Oct 27 '12 at 13:14

KekuSemau

6,830
4
24
34

Thanx! It worked for me. And what if I want to get the date of tomorrow, and of the day after tomorrow? What do I have to change? – Jonas Nov 01 '12 at 08:23
Think of DATE() as the beginning of that day (=0:00), so tomorrow is `BETWEEN DATEADD('d', 1, DATE()) AND DATEADD('d', 2, DATE())` and so on. (Then again, if you want to skip weekend days automatically I would maybe think again about doing that rather in the application code...) – KekuSemau Nov 01 '12 at 09:44
Oh, and if PatientDate is without time (=it always has a time component in MS Access, but it is 0:00): `WHERE PatientDate = DATEADD('d', 1, DATE())` for tomorrow... – KekuSemau Nov 01 '12 at 09:46

score 0 · Answer 3 · answered Oct 27 '12 at 12:23

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

score 0 · Answer 4 · answered Oct 27 '12 at 13:05

Use the DATEADD and DATEDIFF functions to remove the time from the date:

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

score 0 · Answer 5 · answered Jan 05 '17 at 14:52

0

Try this

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

or

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

answered Jan 05 '17 at 14:52

Ad Kahn

551
4
6

Get data from database WHERE date is today VB.net

5 Answers5