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

Question

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:

1. Stop using concatenated strings for your queries (lookup SQL injection) 2. Use parameters (look [here](https://stackoverflow.com/a/37129888/800214) for example 3. all your problems are solved... — whosrdaddy, Jul 31 '19 at 06:53
4. `uqMyTimesheet.SQL.Clear;` is pointless. Setter of property `Text` calls `Clear` within `BeginUpdate` .. `EndUpdate` block. — Peter Wolf, Jul 31 '19 at 08:20

Gianluca Colombo · Answer 1 · 2019-07-31T14:06:19.753

3

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;

edited Jul 31 '19 at 14:06

answered Jul 31 '19 at 08:22

Gianluca Colombo

717
17
38

I appreciate the share of doing this better. I tried your code but I am not getting any response, no error message even. So, I tried to change the assigned variable exactly the same format with database '2019-01-01' and I am getting the error instead as — '2019-01-01' it is not a valid date. I've been reading date format issues but it is getting me nowhere. I know the date is a bit complicated, perhaps for me only. Need some more help here. Thanks. – Mel Jul 31 '19 at 11:30
Please, share a stamp of dataset, then "mytimesheet" fields type also, to better understand where the error is – Gianluca Colombo Jul 31 '19 at 13:08
I have updated my query above. Hope these are the ones you were looking for. Otherwise, let me know. – Mel Jul 31 '19 at 13:23
I need just a stamp of a recordset result from "SELECT DATE FROM mytimesheet LIMIT 10" – Gianluca Colombo Jul 31 '19 at 13:33
Uploaded.. please check again above. – Mel Jul 31 '19 at 13:38
I've just posted another approach. Let me know please. – Gianluca Colombo Jul 31 '19 at 14:06
1

Why not declare StartDate and EndDate as `TDate`? Then, instead of trying to force fit the format, use the first example above, passing the DATE to the parameter. Also, really bad form to have a column named "Date". If you control that, label it something like "tsDate". – JacalarRick Jul 31 '19 at 17:16
1

Make the query use `TParam.AsDate` and then wrap the query in a function that takes `TDate` values as input. That way, the code is agnostic to the formatting that the DB uses for date values. Make the caller decide how best to produce valid `TDate` values to pass in however it wants. For instance, by using a `TDateTimePicker` instead of a text input like `TDBDateTimeEditEh`. Or using `EncodeDate()` instead of `StrToDate('')`. – Remy Lebeau Jul 31 '19 at 17:38
@RemyLebeau can you give a quick code of what your saying here. It is actually new to me. I tried but I am getting nowhere with the function. – Mel Aug 01 '19 at 21:08
@Mel see the answer I just posted – Remy Lebeau Aug 01 '19 at 23:54

score 0 · Answer 2 · answered Aug 01 '19 at 23:53

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;

Wow! @RemyLebeau Works like a charm! Thank you so much! Got it perfectly and additional knowledge. However, I cannot vote for you. I'll vote on this little later when I have more points. — Mel, Aug 02 '19 at 08:53

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

2 Answers2