how to query DBF(dbase) files date type field in where and between clause

Question

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,

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 syntax Error: missing operator

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 exception, but does not return any records though there are matching records.

The same things happens for the where clause as well. What can I do to filter records from a range

I'm using 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);
}

Solution 1

OK -

I believe the problem is that dBase/xBase doesn't support "between" for SQL dates

WORKAROUND:

@"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 dBase syntax supports "date" literals (e.g. "2/16/2006"; I don't know about "datetime" - plese try it and see.

Solution 2

dbase/Foxpro syntax for BETWEEN (and works on any of same data types) is

BETWEEN( SomeValue, MinRange, MaxRange )

so your query could be done like

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

in old DBase and VFP forward, CTOD() stands for Character To Date conversion and expects in format like mm/dd/yyyy. By doing just the "date" portion, it always implies 12:00:00 AM.

in addition, you could use the DATE() function available such as date( yyyy, mm, dd ) as Date( 2006, 02, 16 ) which would also return this specific date.

If you wanted a specific time, you could use CTOT() which represents Character TO dateTime field, such as

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

Solution 3

Not an DBase expert but this and this looks promising.

Solution 4

This worked for me,

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

I checked this with OLEDB Connection

View more solutions