SearchCriteria whit oracle database

rbuser

I'm using D6 and RB7.02 and ORACLE database.

I'm create a report whith searchcriteria "between", the type of
searchcriteria is 'DATE'

This is the query generate by the report :

select
table.date
from
table
where
date between convert(datetime,'2003-05-28 09:53:27',120) and
convert(datetime,'2003-05-28 09:53:27',120)

when i execute this query with, i'm an error.

Is it normaly, and what's the solution for resolve this error?

Thank's.

digitalmetaphors

RB converts the criteria. You can change the conversion of the date format
by first looking in RBuilder\Source and examine daSQL.pas in the
ResolveCriteria method on the lines below to convert to your date format.

{add special function call to convert the Oracle date based on
the format of the criteria value}
if (IsOracle) then
begin
if (lbContainsTime) then
Result := 'TO_DATE(' + '''' + Result + '''' + ',' + ''''
+ lsDateFormat + ' HH24:MI:SS' + '''' + ')'
else
Result := 'TO_DATE(' + '''' + Result + '''' + ',' + ''''
+ lsDateFormat + '''' + ')';
end;


You'll see that before these lines of code, there is a call on the session
to get the date format. You can override this behavior in your DADE plugin.
For example, if you are using daDOA, add and override to the TdaDOASession
class to return the format that you want to use. This way you do not have to
change the RBuilder\Source, just the daDOA DADE plugin.

function GetSearchCriteriaDateFormat(aDatabaseType: TppDatabaseType; const
aDatabaseName: String): String; override;

If not, thenyou'll have to change the source to ResolveCriteria and point
your library path to RBuilder\Source to achieve the formatting you need for
your database.

Cheers,

Jim Bennett
Digital Metaphors