Customising the date format for DADE reports

rbuser

Our company is based in UK, and we use a "standard" date format of
'DD/MM/YYYY HH24:MI:SS' for our Oracle databases. I have noticed the
following about RB:
(1) In DADE, the Query Designer builds its SQL using a date format of
MM/DD/YYYY HH24:MI:SS
(2) For reports using AutoSearch, inputted date values are validated against
the PC's regional setting for Short Date
Therefore, to use all RB features I would need to set a "non-standard" date
format on the database (Oracle NLS_DATE_FORMAT) and modify the PC's regional
settings
Could you please give me a hint about customising the Query Designer to
apply our (UK) date format

Martin Ramshaw

Analyst Programmer

Wheatley Associates Ltd

Bacton Technology Park, Broad Road, Bacton. Suffolk. IP14 4HN UK

http://www.wheatley-associates.co.uk

mailto:martin.ramshaw_nospam@wheatley-associates.co.uk

nardmoseley

The following article contains information on how dates are handled and how
you can control the formatting.

-------------------------------------------------
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.





--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com