Type mismatch in expression in Delphi 7 on SQL append

Question

I have a code, that checks the current date when a form is loaded, performs a simple calculation, and appends a SQL in Delphi. It works on Windows 7 with Delphi 7, and on another computer with Xp, but doesn't on 3 other computers with Xp. When the form is loaded it shows a "Type mismatch in expression", and points to the line after the append. What could be the problem?

procedure TfmJaunumi.FormCreate(Sender: TObject);
var d1, d2: TDate; 

begin  
 d1:= Date;  
 d2:= Date-30;  
 With qrJaunumi do
     begin
         Open;
         SQL.Append('WHERE Sanem_datums BETWEEN' + #39 + DateToStr(d1) +
         #39 + 'AND' + #39 + DateToStr(d2) + #39);
         Active := True; 
     end; 
end;

Answer 1

It's almost certainly to do with the local internationalisation settings on those computers - DateToStr will return a string in the local date format (possibly MM/DD/YYYY or DD/MM/YYYY) - and depending on where you are this might not be what you're expecting.

I suspect you'll find that the computers it's not working on think they're in a different country/use a different internationalisation setting to the computers where it's working.

A better solution would be to use FormatDateTime to get the date into a standard format that your SQL Server installation will accept, so there's no chance that any local 'internationalisation' settings can interfere like this.

Answer 2

You may use a prepared statement to overcome any localization problems with date time values. DateToStr depends on the client side. FormatDateTime can fail if the server's localization doesn't accept the date format.

procedure TfmJaunumi.FormCreate(Sender: TObject);
var
  d1, d2: TDate;
begin
  d1:= Date;
  d2:= Date - 30;
  //qrJaunumi.SQL.Clear; removed because it would remove the "SELECT ... FROM ..." part
  qrJaunumi.SQL.Add('WHERE Sanem_datums BETWEEN :StartDate AND :StopDate ');
  qrJaunumi.Prepared := True;
  qrJaunumi.ParamByName('StartDate').AsDateTime := d1;
  qrJaunumi.ParamByName('StopDate').AsDateTime := d2;
  qrJaunumi.Open; // = qrJaunumi.Active := True;
end;

The space after ":StopDate" is important because Delphi has a bug in the parameter parser unless they fixed it in newer versions.

Answer 3

As robsoft says, it is probably the internationalisation settings. You could use parameterised queries instead - they are generally simpler if using dates and times.

Also, the Open after the with's begin isn't needed - in fact it will open the query without the WHERE clause you are adding.

 procedure TfmJaunumi.FormCreate(Sender: TObject);

 var d1, d2: TDate; 

 begin  d1:= Date;  d2:= Date-30;  With
 qrJaunumi do
         begin
         SQL.Append('WHERE Sanem_datums BETWEEN :StartDate AND :EndDate');
         // exact expression will vary according to DB connection type.
         // Example is for TADOQuery.
         Parameters.ParamByName('StartDate').Value := d1;
         Parameters.ParamByName('EndDate').Value := d2;
         Active := True; 
         end; 
 end;

Answer 4

Unfortunately none of the above worked, but the solution was to replace the Format with "yy.mm.dd." instead of "yyyy.mm.dd.", and adding single quotes. Weird, it says the format is "yyyy.mm.dd." everywhere. The code looks like this now:

procedure TfmJaunumi.FormCreate(Sender: TObject);
var d1, d2: TDate; d3, d4, atd: String;

begin
  d1:= Date;
  d3:= FormatDateTime('yy.mm.dd.',d1);
  d2:= Date-30;
  d4:= FormatDateTime('yy.mm.dd.',d2);
  atd := '''';
  With qrJaunumi do
    begin
      Open;
      SQL.Append('WHERE Sanem_datums BETWEEN'+ atd+d4+atd +'AND'+ atd+d3+atd+';');
      Active := True;
    end;
end;