Wednesday, March 04, 2009

ODBC – Using date queries while reading DBF files

Quick Note:

If you use the ODBC driver to read .dbf files and you wish to use a query on a date field to filter the results, then the format of the date in the query should be: {D ‘yyyy-mm-dd’}

And the ConnectionString to use for loading the DBF file using ODBC in .Net is:

@"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB='"
            + folderName + "';Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"

And here is a sample query on a date field:
SELECT * FROM " + fileName.dbf where dob > {D ‘2000-12-31’}

side note: If you wish to run the query on a DataView that has been created from a DataTable that was created using an OdbcDataAdapter, then the same query becomes (‘mm/dd/yyyy’): dob > ‘12/31/2000’

No comments: