How to request data like DBF date type (dbase), where and between point

Question

I have a DBF file and am trying to read it from C # code. I can read files successfully without applying any conditions or applying conditions to fields of type varchar. My problem is that I have to filter the entries from the Date (type: date) field. I tried the following methods,

SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between 2/16/2006 12:00:00 AM and 2/20/2006 12:00:00 AM 

gives syntax above Error: missing statement

 SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between '2/16/2006 12:00:00 AM' and '2/20/2006 12:00:00 AM' 

above gives a data type mismatch error

 SELECT * FROM D:\DBFreader\file.dbf where [RDATE] between 2/16/2006 and 2/20/2006 

Above does not throw any exceptions, but does not return any records, although there are corresponding records.

The same thing happens for the where clause. What can be done to filter records from a range

I use the following code to read it

 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); } 

Answer 1

OK -

I believe the problem is that dBase / xBase does not support "between" for SQL dates

Temporary solution:

@"SELECT * FROM D:\DBFreader\file.dbf where [RDATE] >= #2/16/2006 12:00:00 AM# and [RDATE] < #2/20/2006 12:00:00 AM#"

PS: I know that dBase syntax supports "date" literals (for example, "2/16/2006", I don't know about "datetime" - try it and see.

Answer 2

Syntax

dbase / Foxpro for BETWEEN (and works with any of the same data types)

 BETWEEN( SomeValue, MinRange, MaxRange ) 

so that your request can be executed as

 where between( [RDATE], ctod( '02/16/2006' ), ctod( '02/20/2006' )) 

in old DBase and VFP forward, CTOD () means converting characters to a date and expects it in the format mm / dd / yyyy. Performing only the “date” part, this always implies 12:00:00.

Alternatively, you can use a DATE () function such as date (yyyy, mm, dd) as the date (2006, 02, 16), which will also return that specific date.

If you need a specific time, you can use CTOT (), which represents the Character TO dateTime field, for example

 CTOT( "2012-09-20T15:16:21" ) = Sep 20, 2012 @ 3:16:21pm 

Answer 3

Not a database expert, but this and this one look promising.

Answer 4

It worked for me

 WHERE RDATE >= {^2016-11-01} AND RDATE <= {^2017-01-31} 

I checked this with OLEDB Connection