DateTime formats

rbuser

I notice the TppAutoSearchField.SearchExpression seems to require dates
be entered in US date format on my machine (which is OK - I have my
machine configured to use US localization settings). I'm just wondering
if this is always the case (as with some daft databases I've used), or
if an end user is running ReportBuilder on a machine configured to use
dd/mm/yy date format, will the search expression expect date formats in
that format?

The reason I ask is that I'm assembling this Search Expression in
code... maybe I shouldn't be doing that.

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net

nicocizik

James,

Check out the article below.

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


--

rbuser

Cheers Nico, looks like exactly what I'm after. So, just to make sure
I've got this right, if I'm modifying Criteria values via the TdaSQL
object (which I am), I should EITHER
1. Provide date values using the default date format corresponding to my
database below OR
2. Override the GetSearchCriteriaDateFormat and
GetSearchCriteriaTimeFormat methods to return the date format that I'm
using? Correct?

How would I go about option number 2... where would I override these
methods?

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net

rbuser

> 1. User entry

Hmmm... well this is a bit of a problem. I'm trying to work with
criteria in code. I need to know the default date value for a report
criterion in YYYY-MM-DD format.

However, the value that I'm getting from daSQL.Criteria[i].Value appears
to be in the format that it was entered at the time of user entry (On my
machine, I'm getting 1/13/2003, for the 13th Jan - which is certainly
not YYYY/MM/DD format). Since I'm accessing these values in code, on the
server - which is not necessarily the same machine that was used to
design the reports, I have no idea what the language setting was of the
user that created the report and so I can't tell the difference between
values like '1/2/2003' and '2/1/2003'.

Is there no way to get the value as a variant, rather than a string
(which is what daSQL.Criteria[i].Value returns)?

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net

nicocizik

James,

Currently there is no way to get the value as a variant. The
daSQL.Criteria[].Value can only be a string. This is definitely on our
to-do list for future enhancements.

--

rbuser

Nico Cizik (Digital Metaphors) wrote:

Hi Nico,

And this string is stored as it was supplied at the time of report
creation?

If so, then I might do well to override the method that you mentioned
earlier and redefine the date/time format as YYYY-MM-DD. If not, it's
often impossible to tell what the date is from this string.

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net

nicocizik

James,

You can try changing our source to not perform the date-time conversion but
rather always save it down in a certain format. See TdaSQL.ResolveCriteria
in the daSQL.pas file.

--

rbuser

Cheers Nico, will do.

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net