Dates in "Search" clause

rbuser

Hello,

Yesterday i tried to get the end-user tool with a search on BirthDate.
The PC is running a dutch version of Windows XP
ReportBuilder end-user is running in English (whatever that has to do with
it).

When entering a date as a search value , the search value is not accepted
because rb says it is not a valid date,
or when it accepts the date, i get a conversionerror from the sql statement
created.

Eric

nicocizik

Hi Eric,

Which database are you connecting to? If possible let me know the exact
steps I can take to recreate the issue using a demo database perhaps (or a
table I can create on my own).

--
Thanks for supporting ReportBuilder! Please vote for ReportBuilder in the
Delphi Informant Readers Choice awards!

http://www.delphizine.com/ballot2004/

rbuser

Hello Nick,

I'm using Interbase 6.0.2.0.

My windows settings for dates: dd-m-yyyy

My application set the following values in Delphi:

ShortDateFormat := 'DD-MM-YYYY';
LongDateFormat := 'DD-MM-YYYY';
ShortTimeFormat := 'HH:NN';
LongTimeFormat := 'HH:NN:SS';
DateSeparator := '-';

I Have a report with a search on a date field (IB datatype DATE)

When entering an incorrect date (in dutch dd-mm-yyyy) like 12-31-2004 then
the form show me that i entered a wring date (in red)
When entering a correct date like 31-12-2004 , then the form closes and the
query is executed and i get
the error: "Conversion error from string "12/31/2004 00:00:00"

Removing Delphi's code still does not solve the problem.
Even on entering a value like 1-1-2004 gives the conversionerror.

Eric

rbuser

Hello Nick,

I tried it using a windows settings of M/d/yyyy but it still does not work
By the way i use version 7.03

Eric

nicocizik

Hi Eric,

I found an article that may be of some help to you. It explains in detail
how dates are handled in DADE.

-------------------------------------------------
Tech Tip: Date formats used by DADE
-------------------------------------------------

DADE handles in two phases:


1. User entry

This occurs when the user specifies a date using the Query tools or the
AutoSearch dialog. DADE converts the date string entered by the user into a
Delphi TDateTime value. The string entered by the user is converted using
Delphi's StrToDateTime. By default Delphi's StrToDateTime relies on the date
formatting variables for the current windows locale. These can be overridden
by the developer. See Delphi online help for StrToDateTime for more
information.


2. SQL submitted to the server

When generating the SQL to be submitted to the database server, DADE
converts the TDateTime value from number 1 above, to a string using the
Delphi's FormatDateTime function. The format string used to convert the
TDateTime is specified by the TdaSession.GetSearchCriteriaDateFormat and
TdaSession.GetSearchCriteriaTimeFormat functions. These are virtual methods
which may be overridden by descendant TdaSession classes.

Below are the default values returned.


{---------------------------------------------------------------------------
---}
{ TdaSession.GetSearchCriteriaDateFormat }

function TdaSession.GetSearchCriteriaDateFormat(aDatabaseType:
TppDatabaseType; const aDatabaseName: String): String;
begin

{return a format usable by FormatDate}
case aDatabaseType of

dtMSAccess:
Result := 'YYYY-MM-DD';

dtMSSQLServer, dtSybaseASA, dtSybaseASE, dtOracle:
Result := 'YYYY/MM/DD';

dtAdvantage:
Result := 'YYYY-MM-DD';

else
Result := 'MM/DD/YYYY';

end;

end; {function, GetSearchCriteriaDateFormat}

{---------------------------------------------------------------------------
---}
{ TdaSession.GetSearchCriteriaTimeFormat }

function TdaSession.GetSearchCriteriaTimeFormat(aDatabaseType:
TppDatabaseType; const aDatabaseName: String): String;
begin
{return a format usable by FormatDateTime}
case aDatabaseType of

dtMSAccess:
Result := 'HH::MM::SS';

else
Result := 'HH:MM:SS';
end;

end; {function, GetSearchCriteriaTimeFormat}



Note: DADE augments the above with additional formatting delimiters for
Oracle and MSAccess. See TdaSQL.ResolveCriteria located in
RBuilder\Source\daSQL.pas for more information.

--
Thanks for supporting ReportBuilder! Please vote for ReportBuilder in the
Delphi Informant Readers Choice awards!

http://www.delphizine.com/ballot2004/

rbuser

Hello Nick,

I looked into the rb code (daDB.pas) as you added and i see no special code
for database type dtInterbase!
How come?

This is the code Interbase needs:

select * from employee where birthdate > '12/31/1959'

So the problem is dat the time part is added to the date part, Interbase
date field do not support time part!

Perhaps the Interbase feature could be added in the next version.

Eric

rbuser

Hello Nick,

I found the problem in my program, the field was declared (in RB_FIELD as
datatype dtDateTime which should be dtDate, that solved my problem.

It took some time but most of the time is your own mistake )

Thanks for your reactions.

Eric