CODEWITHSUNDEEP
vb.net
pankaj babbar
pankaj babbar

Reputation: 69

how to search by single date in ms access database?

i want to search record from ms access database by date. i have stored date in ms access table as "8/23/2020 5:31:21 PM". i am getting syntax error. how can i do this?

    Dim x As Date = DateTimePicker1.Value
 
    Dim command As New OleDbCommand("select col1,col2,col3 where table1.datetime = @datetime", connection)

    command.Parameters.AddWithValue("@datetime", x.ToString("mm/dd/yy"))

code suggested by Pete -S-

and its working.

in this i have changed datatype to shortdate in ms access

    Dim x As Date = DateTimePicker1.Value
 
    Dim command As New OleDbCommand("select col1,col2,col3 From table1 where ([datetime] >= @startDate And [datetime] <= @endDate)", connection)

     Dim dateParameter As OleDbParameter = command.Parameters.Add(New OleDbParameter("@startDate", OleDbType.Date))
     dateParameter.Value = x.Date

     dateParameter =  command.Parameters.Add(New OleDbParameter("@endDate", OleDbType.Date))
     dateParameter.Value = DateAdd(DateInterval.Day,1,x.Date)

Upvotes: 0

Views: 236

Answers (1)

Pete -S-
Pete -S-

Reputation: 562

@Steve pointed out the time issue, when searching dates that include time with the date, one solution would be to search the entire range of the day:

Option 1: table1.datetime >= '8/23/20' And table1.datetime < '8/24/20'

Dim command As New OleDbCommand("select col1,col2,col3 From table1 where ([datetime] >= @startDate And [datetime] < @endDate)", connection)
    
        command.Parameters.AddWithValue("@startDate", x.Date)
        command.Parameters.AddWithValue("@endDate", DateAdd(DateInterval.Day,1,x.Date))

Preferred method of adding parameters, explicitly set the data type when adding the parameter:

Dim dateParameter As OleDbParameter = command.Parameters.Add(New OleDbParameter("@startDate", OleDbType.Date))
dateParameter.Value = x.Date

dateParameter =  command.Parameters.Add(New OleDbParameter("@endDate", OleDbType.Date))
dateParameter.Value = DateAdd(DateInterval.Day,1,x.Date)

If x = 8/23/2020 Then Your SQL that executes will be select col1,col2,col3 From table1 where (table1.datetime >= '8/23/20' And table1.datetime < '8/24/20')

This will include all times on 8/23/2020 between 00:00:00 and 23:59:59... (before the next day). If you do not specify the time, 00:00:00 is presumed. That is why equality and time don't play out as you think it should when searching.

Option 2: table1.datetime >= '8/23/20' And table1.datetime <= '8/23/20 23:59:59' Explicitly add the time to the search value. This will work 99.99% of the time unless there is a value stored that has a time stamp between 23:59.59.0001 and 00:00:00.0000 that could be filtered out.

Upvotes: 2

Related Questions