How to select records between 2 DateTimes

Question

I have tried

SQLCommand = "SELECT * 
              FROM myTable 
              WHERE recDateTime 
                    BETWEEN '30/12/2013 23:49:09' AND '31/12/2013 00:01:29'"

But it returns a "Data type mismatch in criteria expression." OleDb Exception. Am I doing anything wrong here? Because I can't seem to see the problem.

recDateTime is a DateTime field. And those two dates are DateTimes

I am using OLEDB in VB.NET.

On a side note, those two times are not in the future of my TimeZone and they contain 4 records. — Thahleel al-Aleem, Dec 31 '13 at 04:26
"doesn't work" is a poor description of the issue you are having. — chiliNUT, Dec 31 '13 at 04:29
there we go.try a format of `2013-12-30 23:49:09` instead. http://technet.microsoft.com/en-us/library/bb677303.aspx — chiliNUT, Dec 31 '13 at 04:31
What type of database is OLEDB connected to? It would be nice if things were the same for all DBs but that isn't always the case. You may need to go look for help specific to the DB product. — rheitzman, Dec 31 '13 at 16:21
If this is SQLServer, then use dates in format like this '2013-12-30 23:49:09.000' Note the single quotes and the milliseconds — APrough, Dec 31 '13 at 18:45

score 0 · Answer 1 · edited Feb 25 '15 at 18:48

0

SQLCommand = "SELECT * 
              FROM myTable 
              WHERE  format(recDateTime,'dd/MM/yyyy') 
                     BETWEEN '30/12/2013' AND '31/12/2013'"

Try this Code

SELECT * 
FROM   wwwh 
WHERE  Format(entry_date, 'MM/dd/yyyy hh:NN:ss tt') BETWEEN 
       '12/10/2013 09:00:00 AM' AND '12/11/2013 10:00:00 AM'

edited Feb 25 '15 at 18:48

HaveNoDisplayName

8,291
106
37
47

answered Dec 31 '13 at 04:33

senthilkumar2185

2,536
3
22
36

I need the exact datetime and not just the date. – Thahleel al-Aleem Dec 31 '13 at 04:36
what purpose u need exact time – senthilkumar2185 Dec 31 '13 at 04:50
Because there is records outside this time range and I don't want those to display. – Thahleel al-Aleem Dec 31 '13 at 04:51

score 0 · Answer 2 · edited May 23 '17 at 11:43

There is a fair chance that the database is trying to interpret the literal date strings in en-US format. You can eliminate that problem entirely by using parameters in the query, like this:

Dim oledbSql = "SELECT * FROM myTable WHERE recDateTime BETWEEN ? AND ?"
Dim oledbSqlCmd As New OleDbCommand(oledbSql, oledbConn)
Dim dtStart As New DateTime(2013, 12, 30, 23, 49, 9)
Dim dtEnd As New DateTime(2013, 12, 31, 0, 1, 29)

oledbSqlCmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@Start", .OleDbType = OleDbType.Date, .Value = dtStart})
oledbSqlCmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@End", .OleDbType = OleDbType.Date, .Value = dtEnd})
' now execute the query

You don't actually have to name the parameters, but it makes it easier to see what is happening (which aids maintainability). You do, however, have to add them in the order in which they are used as a ? is used as a placeholder for each parameter.

If you are using SQL Server as the database, it is better (because it is optimised for it) to use System.Data.SqlClient rather than System.Data.OleDb, and you can then use named parameters in the query:

Dim sql = "SELECT * FROM myTable WHERE recDateTime BETWEEN @Start AND @End"
Dim sqlCmd As New SqlCommand(sql, sqlConn)
Dim dtStart As New DateTime(2013, 12, 30, 23, 49, 9)
Dim dtEnd As New DateTime(2013, 12, 31, 0, 1, 29)

sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Start", .SqlDbType = SqlDbType.DateTime, .Value = dtStart})
sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@End", .SqlDbType = SqlDbType.DateTime, .Value = dtEnd})
' now execute the query

Please don't ever be tempted to use .AddWithValue as it will make assumptions about the data type which will foul things up unexpectedly.

Also, you should (almost) always explicitly state the columns you want from the database instead of using * because the database is free to return the columns in any order it wants to - which may not be the order you think they will be returned in.

Depending on the actual database you are using, parameter names might need to have something like a : instead of an @ as a prefix.

[I thought I might as well answer the question rather than only be negative about other proposed answers.]

Chris Fung · Answer 3 · 2015-02-25T19:31:50.140

-3

 Dim da As OleDbDataAdapter = New OleDbDataAdapter(
            "SELECT * 
            FROM TableID
            WHERE (Format(ColumnID, 'hh:NN:ss tt') 
                  BETWEEN '00:00:00 AM' AND '01:00:00 AM') And [ColumnID] = True ",
  "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source =Database1.mdb")

edited Feb 25 '15 at 19:31

answered Feb 25 '15 at 18:19

Chris Fung

79
1
4

1

Is this an answer to a different question? – Andrew Morton Feb 25 '15 at 18:30
You should at least format your answer to match the variables used by the author. – KHeaney Feb 25 '15 at 18:42
eg: 00:18:00 AM = 12:18:00 AM 3:00:00 PM = 3:00:00 PM – Chris Fung Feb 25 '15 at 19:28

How to select records between 2 DateTimes

3 Answers3