toolbarsearch with datetime problem

[MarcoRu]

Hi all, I'm having a problem with toolbarsearch and datetime fields. If I try to filter my grid with a datetime column as shown in this picture

SearchDate1.jpg

it doesn't work. From debug i can see this:

SearchDate2.jpg

As you can see the date passed is treated as a string, so it doesn't work. someone has already had this problem? as it has solved?
P.S.: on database the field is datetime

This is the result:

SearchDate3.jpg

Another problem is this, if I type a second time a date, from debug I can see this:

SearchDate4.jpg

Alternately, the date is passed with the quotes, the next time without quotes, and so on.

P.S.: I'm using 2.3.0.69 version

[Jorge Sousa]

Hi

You have to use Provider.OnRuleToSQL event for a non ansi SQL

[MarcoRu]

There is a way to use a datetime picker instead of writing a date in the filter?

[Jorge Sousa]

Hi

Unfortunatelly there is not such possibility

[MarcoRu]

Hi,
I still have the same problem. With all columns of my grid, this code works perfectly

Code: Select all

procedure TframeMails.gridDatProvMailSearchSQL(Sender: TObject; AData: TIWCGJQProviderSearchData; const ASQL: string);
var
  SQL, sOrderByClause: string;
  iPos: Integer;
begin
  SQL := ASQL;
  sOrderByClause := '';
  iPos := 0;

  try
    qryMail.Close;
    iPos := Pos('ORDER', UpperCase(qryMail.SQL.Text));
    if (iPos > 0) then
    begin
      sOrderByClause := Copy(qryMail.SQL.Text, iPos);
      sOrderByClause := StringReplace(sOrderByClause, #13#10, '', [rfReplaceAll]);
    end;
    qryMail.SQL.Clear;
    qryMail.SQL.Add('SELECT * FROM view_GUI_EMails_Din');

    if (SQL <> '') then
    begin
      if (Pos('WHERE', qryMail.SQL.Text) > 0) then
        qryMail.SQL.Add('AND ' + SQL)
      else
        qryMail.SQL.Add('WHERE ' + SQL);
    end;
    qryMail.SQL.Add(sOrderByClause);
    qryMail.Open;
    Application.ProcessMessages;
  except
    on e: exception do
    begin
      qryMail.Close;
      IWCGMessageDlg('ECCEZIONE: ' + e.Message, mtError, [mbOK],
        procedure(Dialog: TIWCGJQMsgDialog; AResult: TModalResult)
        begin
        end);
    end;
  end;
end;

except for datetime columns.
In RuleToSql event I can only check the operators. How can I check datetime columns and modify them to work? There is an example? If I do this in RuleToSQL event, my event OnSearchSQL continues to work?

[MarcoRu]

Hi all,
I found a solution for my case. I implemented this in OnRuleToSql event, only for datetime filter:

Code: Select all

function TframeMails.gridDatProvMailRuleToSQL(Sender: TObject; ARule: TIWCGJQProviderSearchRule;
  var Handled: Boolean): string;
var
  sOper: string;
  fs: TFormatSettings;
  dOutDate: TDateTime;
begin
  sOper := '';

  if (Assigned(qryMail.FieldByName(ARule.Name))) then
  begin
    if (qryMail.FieldByName(ARule.Name).DataType = ftDateTime) then
    begin
      fs.DateSeparator := '/';
      fs.ShortDateFormat := 'dd/mm/yyyy';

      if (TryStrToDate(ARule.Value, dOutDate) = true) then
      begin
        case ARule.Oper of
          sopEqual:
            begin
              sOper := '=';
            end;
          sopGreater:
            begin
              sOper := '>';
            end;
          sopLess:
            begin
              sOper := '<';
            end;
          sopLessEqual:
            begin
              sOper := '<=';
            end;
          sopGreaterEqual:
            begin
              sOper := '>=';
            end;
        end;
        Result := 'CONVERT(Date, ' + ARule.Name + ', 103) ' + sOper + ' Convert(Date, ''' + ARule.Value + ''', 103)';
        Handled := true;
      end
      else
      begin
        Handled := false;
        RenderRegionAsync(IWCGJQRegionTop, false, true);
      end;
    end;
  end;
end;

[MarcoRu]

Hi

Unfortunatelly there is not such possibility

Hi, is still not possible use a datetime picker instead of writing a date in the filter?

[Jorge Sousa]

Hi

No, it's still not possible, I'm sorry.