Macro of MyScript for TDateTime type not working

[Jonathan Liaw]

Hi,
The macro of MyScript for TDateTime type is not working in my following code. My SQL statement is as follow:

SELECT * FROM SHTrx WHERE TrxDate BETWEEN &FromDate AND &ToDate;

procedure TForm1.Button1Click(Sender: TObject);
begin
MyScript1.SQL.Assign(Memo1.Lines);
MyScript1.MacroByName('FromDate').AsDateTime := FromDate.DateTime;
MyScript1.MacroByName('ToDate').AsDateTime := ToDate.DateTime;
MyScript1.Execute;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
MyScript1.SQL.Assign(Memo2.Lines);
MyScript1.Execute;
end;

It does not work but it work if I use the following SQL instead:
SELECT * FROM SHTrx WHERE TrxDate BETWEEN '2004-01-01' AND '2004-12-31';

Please help. Thanks
If you need the test program, please email to me.

Jonathan Liaw

[Paul]

MyDAC uses ShortDateFormat, LongTimeFormat global variable for receiving string value from TDateTime. You can set ShortDateFormat = 'yyyy-mm-dd', LongTimeFormat='' in your example before assigning value with AsDataTime

[Jonathan Liaw]

Hi Paul,
Thanks you for your reply. Your solution works but becasue our environment not allow us for such a change during runtime. Can I suggest to you that you modify your source code to as follow becasue we think such format is more suitable due the requirement by MySQL.

Jonathan Liaw
TOB Solution

dbaccess.pas
Line: 5040

Your old code:

procedure TMacro.SetAsDateTime(Value: TDateTime);
begin
Self.Value := '''' + DateTimeToStr(Value) + '''';
end;


New Code:

procedure TMacro.SetAsDateTime(Value: TDateTime);
begin
Self.Value := '''' + FormatDateTime('YYYY-MM-DD', Value) + '''';
end;

[Ikar]

Your suggestion will touch another our products (SDAC and ODAC). From our point of view it'd be correct to set values for these macros not in DateTime format but in String format at once.