Issue 8847 - No date criteria with ODBC
Summary: No date criteria with ODBC
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.1
Hardware: PC Windows ME
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2002-10-30 13:55 UTC by timcastle
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description timcastle 2002-10-30 13:55:22 UTC
When connected via ODBC to a Visual FoxPro database, some criteria doesn't 
appear to function.  (example: > #10/01/2002#) The results ignore the filter.  
The behavior is present when editing the query or using database view filters 
(F4) in writer

This problem also exists when connecting via ODBC to an Advantage database.
Comment 1 Frank Schönheit 2002-10-31 09:48:34 UTC
Tim, thanks for using and supporting OOo :)
Do you have the possibility to generate ODBC logs for the process of
executing the filtered query? If so, could you please generate them
and attach them to this issue?

Marc, do we have a FoxPro database to check this?
Comment 2 timcastle 2002-11-04 14:10:10 UTC
Can't generate logs that I know of.  Same results in ODBC to an 
Advantage and Visual Fox Pro data.
Comment 3 timcastle 2002-11-18 16:29:37 UTC
I was able to query the date field on a Visual Fox Pro database after 
asking a programmer for the proper date format. See Sample below.  
The issue is that OOo doesn't allow that format for dates and changes 
it to {D '2002-10-01' } which is ignored by the database.  In 
addition, the between command gives me a syntax error.  I have to 
switch off the design view and run the SQL command directly. I can 
enter BETWEEN '10/01/2002' AND '10/31/2002' in design view and then 
switch to SQL view and change the ' characters to { or } this is a 
work around.

Sample of format that works with Visual Fox Pro Database in SQL view 
only: BETWEEN {10/01/2002} AND {10/30/2002}

As for the Advantage database, the following format works in design 
view: BETWEEN '10/01/2002' AND '10/31/2002' 
Comment 4 Frank Schönheit 2002-11-19 08:03:33 UTC
well, that may be a problem ...

The formats you gave are not SQL-92 compliant, so OOo can not easily
use them on _any_ ODBC data source (because they are
database-specific, and OOo is to expected to work with more than
FoxPro and Advantage :).

Instead, OOo uses the {D...} notation, because this is the required
ODBC syntax, and in this sense, it's a bug of the ODBC drivers not
recognizing it.

We could perhaps add code for optional alternative date formattings ...
Comment 5 timcastle 2002-11-19 15:45:26 UTC
Thanks for your help.  The programmer told be that the Visual Fox Pro 
data was a bit unique.  As for the second format 

BETWEEN '10/01/2002' AND '10/31/2002'

It DOES work in design view and SQL view OOo as shown.
Comment 6 Frank Schönheit 2002-11-19 16:01:24 UTC
Tim, so the bug "vanished" for you?
Comment 7 timcastle 2002-11-20 15:37:43 UTC
It has sort of "vanished". I think what would be good is if OOo 
warned you of non-compliant formulas, but gave you the option to 
launch the query anyway.

Or... simply add the ability to accept:
BETWEEN {10/01/2002} AND {10/30/2002}

OOo already accepts: 
BETWEEN '10/01/2002' AND '10/31/2002' 

Or... include the workarounds above to the help section.  (This is 
probably the easiest solution)

Comment 8 marc.neumann 2002-11-28 15:14:47 UTC

So we need a section in the datasource setting to specified more
settings for a datasource to 'workaround' those issues.

Bye Marc
Comment 9 falko.tesch 2003-11-04 16:15:10 UTC
FT: re-targeted to Office later
Comment 10 hans_werner67 2004-02-02 12:52:30 UTC
change subcomponent to 'none'
Comment 11 falko.tesch 2005-10-20 20:20:00 UTC
FT: Re-assigned to requirement default user