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

TdaSQLBuilder.SearchCriteria.Add does not handle date properly

edited August 2007 in Datapipelines
Hi,

I have a terrible depression just thinking of solving this.

My regional setting (in the Windows control panel) for ShortDate is
"mmm/dd/yy"

I need to filter a report with dates, therefore I write below codes:

x := TdaSQLBuilder.Create(ppReport1.DataPipeLine.SQL);
x.SearchCriteria.Add('duty_table','duty_date','operator', DateToStr(Now))

but when I run
x.ApplyUpdates;

it gives an EConvertError!!

I don't want to change my regional settings because my boss instruct me to
write a code that is independent of client PC's regional setting.. ;<

Pls help, TQ
Arif

Comments

  • edited August 2007
    I'm sorry, it doesn't gives EConvert Error yet.


    x := TdaSQLBuilder.Create(ppReport1.DataPipeLine.SQL);
    x.SearchCriteria.Add('duty_table','duty_date','>=', DateToStr(Aug/01/07))

    After I ran
    x.ApplyUpdates;

    I check the SQL
    sSQL := x.SQL.MagicSQLText.Text;

    and it converts my date wrongly!
    sSQL = '...where duty_date >= '08/07/2001'...'



    Then only I try to change the supplied date to the SearchCriteria.Add
    function
    x.SearchCriteria.Add('duty_table','duty_date','>=', DateToStr(01/08/2007))

    and it gives EConvertError.

    Please help, TQ.
    Arif



    ----- Original Message -----
    From: "Arif"
    Newsgroups: digital-metaphors.public.reportbuilder.datapipelines
    Sent: Tuesday, August 07, 2007 11:44 PM
    Subject: TdaSQLBuilder.SearchCriteria.Add does not handle date properly
  • edited August 2007

    - for future reference, please specify Delphi version, ReportBuilder
    version, Windows version, and database product and data access components.

    - the TdaSQLBuilder.SearchCriteria.Add call looks correct and using
    Delphi's DateToStr is correct.

    - check that you are specifying the correct DataSettings.SessionType and
    DatabaseType for the database product that are using. These settings
    determines how the SQL string is generated. Different databse engines
    require different date formats. See point number 2 in the tech tip below.

    - add an entry for RBuilder\Source to your Delphi library path and trace the
    code in daSQL.pas for the method, TdaSQL.ResolveCriteria which contains a
    local function called FormatValue. The FormValue function formats the date
    value based upon the SessionType and DatabaseType.


    -------------------------------------------------
    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
    www.digital-metaphors.com




    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited August 2007
    Thank you.

    I'm using Delphi 7.0, RB 10.04, Windows XP Pro SP2, Interbase 6.1,
    IBExpress.

    Any tips?



  • edited August 2007


    Yes, my prior post included a comprehensive answer. Please re-read my
    complete post.


    DataSettings should be...

    SessionType = IBXSession
    DatabasType = dtInterbase
    SQLType = SQL2


    --
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.