title: how to query DBF(dbase) files date type field in where and between clause
body: <p>I have a DBF file and I'm trying  read it from c# code. I can read files successfully without applying any conditions or applying conditions for varchar type fields.My problem is I have to filter the records from the Date field (type:date). I have tried following ways,</p>
  
  <pre><code>SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between 2/16/2006 12:00:00 AM and 2/20/2006 12:00:00 AM
  </code></pre>
  
  <p>above gives a syntax Error: missing operator</p>
  
  <pre><code>SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between '2/16/2006 12:00:00 AM' and '2/20/2006 12:00:00 AM'
  </code></pre>
  
  <p>above gives a data type mismatch error</p>
  
  <pre><code>SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between 2/16/2006 and 2/20/2006
  </code></pre>
  
  <p>above does not throw any exception, but does not return any records though there are matching records.</p>
  
  <p>The same things happens for the where clause as well.
  What can I do to filter records from a range</p>
  
  <p>I'm using following code to read it</p>
  
  <pre><code>OdbcCommand cmd = new OdbcCommand();
  OdbcDataAdapter da = new OdbcDataAdapter();
  DataTable dt = new DataTable();
  
  using (OdbcConnection connection = new OdbcConnection(connstring))
  {
     connection.Open();
     cmd = new OdbcCommand(@"SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between 2/16/2006 12:00:00 AM and 2/20/2006 12:00:00 AM", connection);
     cmd.CommandType = CommandType.Text;
  
     da.SelectCommand = cmd;
     da.Fill(dt);
  }
  </code></pre>