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