Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

DateTime formats

edited May 2003 in End User
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

Comments

  • edited May 2003
    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.


    --
    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited May 2003
    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

  • edited May 2003
    > 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
  • edited May 2003
    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.

    --
    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited May 2003
    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
  • edited May 2003
    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.

    --
    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited May 2003
    Cheers Nico, will do.

    Best Regards,

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

This discussion has been closed.