'Using a SELECT statement to return a row where a DateTime column = variable of type Date?

I am using OleDb, trying to do this:

Dim d as Date = DateSerial(Year(rptDate), Month(rptDate), 1 - 1)

Dim conn as OleDbConnection = new OleDbConnection(connStr)
Dim cm as OleDbCommand = new OleDbCommand()

conn.Open()
cm.Connection = conn
cm.CommandText = "SELECT * FROM history WHERE ReportDate = " & d

Okay, so I know it returns 1 row because I'm staring at it in SQL Server. Here is what I've tried so far for the query in the last line, none work:

"SELECT * FROM history WHERE ReportDate = " & d
"SELECT * FROM history WHERE ReportDate = " & d.toString("G")
"SELECT * FROM history WHERE ReportDate = '" & d & "'"
"SELECT * FROM history WHERE ReportDate = '" & d.toString("G") & "'"
"SELECT * FROM history WHERE ReportDate = '#" & d & "#'"
"SELECT * FROM history WHERE ReportDate = #" & d & "#" 'These last 2 with toString

I have also tried:

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.Add(d)

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.Add(d.toString("G"))

cm.CommandText = "SELECT * FROM history WHERE ReportDate = '?'"
cm.Parameters.Add(d.toString("G"))

cm.CommandText = "SELECT * FROM history WHERE ReportDate = '?'"
cm.Parameters.Add(d)

Clearly I am missing something here, help? I know that d is the same date as the one in SQL server, specifically 3/31/2011 12:00:00 AM.

sqlvb.nettsqlado.netoledb


Solution 1:[1]

Write

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.AddWithValue("ParamName", d)

Depending on your provider, you may need to use named parameters:

cm.CommandText = "SELECT * FROM history WHERE ReportDate = @date"
cm.Parameters.AddWithValue("date", d)

Solution 2:[2]

Try using another date format in your SQL such as:

Cm.CommandText = "SELECT * FROM history WHERE ReportDate = '" & d.toString("mm/dd/yyyy") & "'"

Solution 3:[3]

Did you mean to create your date variable (d) as the day before the rptDate value (ie. with a day of zero (1-1)) ?

This (below) will get you your record (assuming the record has no time part).

SELECT * FROM history WHERE ReportDate = '" & d.toString("dd-MMM-yyyy") & "'"

(The explicit date format is unambiguous, whereas "G" is not.)

...or...

cm.CommandText = "SELECT * FROM history WHERE ReportDate = @date"
cm.Parameters.AddWithValue("@date", d)

Solution 4:[4]

Your query as stated is asking "give me all the entries for exactly midnight on this date".

Something like this should hopefully work:

cm.CommandText = "DECLARE @MyDate DATETIME; SET @MyDate = ?; SELECT * FROM history WHERE ReportDate >= @MyDate AND ReportDate < DateAdd(day,1,@MyDate);"
cm.Parameters.Add(d)

This will return all entries on or after midnight of the date and before midnight of the next day.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SLaks
Solution 2 Ahmad
Solution 3 Black Light
Solution 4 NYCdotNet

Related Questions