Datatype mismatch when querying DBase Date field via a Delphi ADO Query

Question

I'm trying to resolve a bug in a archaic reporting tool that generates SQL dynamically and I'm running to a problem where I get a Data type mismatch error when the generated SQL queries a Date field from a Dbase table.

I've managed to replicate the same problem in a simple test app where the below query is loaded into a TADOQuery and activated.

SELECT *
FROM [QPERFSAL.DBF] QPERFSAL
WHERE  ( QPERFSAL.PERFDATE = '21/01/2014' )

its obviously related to the date formatting but I've tried numerous formats but I still get the error e.g. dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd etc.

The obvious fix would be to used parameterised queries but as this is generated on the fly by a report tool, I can't use parameters :(

Is there something I'm missing or can I specify the date format at the ADO connection?

Thanks!

Answer 1

Firstly, thanks to all that posted suggestions. Alas, I tried them all but without success :(

thankfully, I found the solution while searching for something unrelated.

    SELECT *
    FROM [QPERFSAL.DBF] QPERFSAL
    WHERE PERFDATE = Format('2014/12/06',"YYYY/MM/DD") 

I'm not sure what effect this will have on localization but at least I can get the query to run now.

Answer 2

Try to use as follows:

SELECT *
FROM [QPERFSAL.DBF] QPERFSAL
WHERE  ( DTOC(QPERFSAL.PERFDATE) = '01/21/2014' )

Answer 3

VFP OleDB Provider I believe also recognizes the DATE() function where you don't need to worry about yyyy-mm-dd or mm-dd-yyyy or dd-mm-yyyy formats. It will build into a proper date format column.

where QPERFSAL.PERFDATE = date( 2014, 1, 21 )

Now, if the "perfDate" column is that of a date/time, then you need to compare based on the date-only portion of the date/time field by using TTOD() (time-to-date function)

where TTOD( QPERFSAL.PERFDATE ) = date( 2014, 1, 21 )