VB.NET - SQL request do not accept = character-CodePudding

This request works great:

Dim cmd1 As New OleDbCommand("SELECT * FROM table1 WHERE dat_recep > #10/14/2022# ORDER BY dat_recep DESC", conn)

It works with >, <, <> but not with =.

It does not generate any error, it just returns a empty table.

I do not want to use something like :

WHERE dat_recep < #10/15/2022# & dat_recep > #10/13/2022#

Any idea why = is not accepted ?

CodePudding user response：

= works as expected - it will allow only rows that match the exact datetime value, 2022-10-14 00:00:00. Any rows whose dat_recep contains a time different than 00:00:00 will be filtered out.

One solution that takes advantage of indexing is to filter for rows with dat_recep greater or equal to 2022-10-14 but less than the next day.

Dim startInclusive As DateTime = somDate.Date
Dim endExclusive As DateTime = startInclusive.AddDays(1)

Dim sql As String = "SELECT * FROM table1 
WHERE dat_recep >= ? 
   AND dat_recep < ? 
ORDER BY dat_recep DESC"

Dim cmd1 As New OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@start",startInclusive);
cmd.Parameters.AddWithValue("@end",endExclusive);