CODEWITHSUNDEEP
mysqldelphi
Mel
Mel

Reputation: 101

Passing MySQL date range query in Delphi using UniQuery with variable string date values gives me returns NULL

I am passing MySQL query using UniQuery in Delphi and it is returing NULL.

I already change the date format in the property of my TDBDateTimeEditEh to match with mysql format yyyy-mm-dd and pass this to my string variable at runtime.

Here's my code:

    procedure TfrmPayroll.Button1Click(Sender: TObject);
    var
      DateRangeQry, StartDate, EndDate : string;
    begin
      StartDate := dthStart.Text;
      EndDate := dthEnd.Text;
      DateRangeQry := 'SELECT * FROM mytimesheet WHERE Date >= '+ StartDate +' AND date <= '+ EndDate +'';

    //ShowMessage(StartDate +' to '+ EndDate); // result yyyy-mm-dd to yyyy-mm-dd

      with dm_u.dmPayroll do
        begin
          uqMyTimesheet.SQL.Clear;
          uqMyTimesheet.sql.Text := DateRangeQry;
          uqMyTimesheet.ExecSQL;
          cdsMyTimesheet.Refresh;
        end;
    end;

I did check the value in the showmessage and it is matching the date format in mysql.

I supposed my codes about would generate range of records from the database within the date range specified but no avail.

I will appreciate any help from you guys.

Updates:

enter image description here

Upvotes: 1

Views: 1361

Answers (2)

Remy Lebeau
Remy Lebeau

Reputation: 595981

Use a parameterized query instead, using TDate instead of string for the parameter values, eg:

procedure TfrmPayroll.QueryTimeSheet(StartDate, EndDate: TDate);
begin
  //ShowMessage(DateToStr(StartDate) + ' to ' + DateToStr(EndDate)); // result yyyy-mm-dd to yyyy-mm-dd
  with dm_u.dmPayroll do
  begin
    uqMyTimesheet.SQL.Text := 'SELECT * FROM mytimesheet WHERE Date >= :PStartDate AND date <= :PEndDate';
    uqMyTimesheet.ParamByName('PStartDate').AsDate := StartDate;
    uqMyTimesheet.ParamByName('PEndDate').AsDate := EndDate;
    uqMyTimesheet.ExecSQL;
    cdsMyTimesheet.Refresh;
  end;
end;

procedure TfrmPayroll.Button1Click(Sender: TObject);
begin
  QueryTimeSheet(dthStart.Value, dthEnd.Value);
end;

Upvotes: 0

Gianluca Colombo
Gianluca Colombo

Reputation: 819

Using parameters instead of OLD concatenated queries style, is better, clear and it works ever! Try this code.

procedure TForm5.Button1Click(Sender: TObject);
var
      DateRangeQry, StartDate, EndDate : string;
begin

      StartDate:='01/01/2019';
      EndDate:='01/01/2020';

      DateRangeQry:='SELECT * FROM mytimesheet WHERE Date >= :StartDate AND date <= :EndDate';
      uqMyTimesheet.SQL.Text:=DateRangeQry;
      uqMyTimesheet.ParamByName('StartDate').AsDate := StrToDate(StartDate);
      uqMyTimesheet.ParamByName('EndDate').AsDate := StrToDate(EndDate);
      uqMyTimesheet.Open;



end;

Another approach when you have different date formats is convert date to string

  StartDate:='2019-01-01';
  EndDate:='2020-01-01';

  DateRangeQry:='SELECT * FROM mytimesheet WHERE DATE_FORMAT(Date, ''%Y-%m-%d'') between :StartDate AND :EndDate';
  UniQuery1.SQL.Text:=DateRangeQry;
  UniQuery1.ParamByName('StartDate').AsString := (StartDate);
  UniQuery1.ParamByName('EndDate').AsString := (EndDate);
  UniQuery1.Open;

Upvotes: 3

Related Questions