Devart Forums

MyDAC Version: 4.40.0.22
MySQL Version: 5.0.41

SQL
Select * From test
where testdate >= :testdatefrom and testdate <= :testdateto

ParamByName('testdatefrom').AsDateTime = DateFrom
ParamByName('testdateto').AsDateTime = DateTo

Last time using MySQL 5.0.33 got results, but update to MySQL 5.0.41 no result return, why?

We couldn't reproduce the problem.
Please specify the definition of your database objects and the script for populating table with data.
Also specify the exact version of Delphi or C++Builder that you use.

Delphi2007 Eng

Script:

DROP TABLE IF EXISTS `test`.`test`;
CREATE TABLE `test`.`test` (
`saleskey` int(10) unsigned NOT NULL auto_increment,
`salesdate` date default NULL,
`amount` decimal(20,2) NOT NULL default '0.00',
PRIMARY KEY (`saleskey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert Into `test`.`test`
Values (Null, '2007-5-17', 100), (Null, '2007-5-18', 120);

---------------------------------------------------------------------
Delphi Program:

*.pas

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, DBGrids, DB, MemDS, DBAccess, MyAccess;

type
TForm1 = class(TForm)
MyQuery1: TMyQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Button1: TButton;
Button2: TButton;
MyConnection1: TMyConnection;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
LdtDateFrom, LdtDateTo: TDateTime;
begin
MyConnection1.Server:= '127.0.0.1';
MyConnection1.Username:= 'root';
MyConnection1.Port:= 3307;
MyConnection1.Database:= 'test';
MyConnection1.Connect;

LdtDateFrom:= EncodeDate(2007, 5, 17);
LdtDateTo:= EncodeDate(2007, 5, 17);

MyQuery1.SQL.Clear;
MyQuery1.SQL.Add('Select * from test'#13#10 +
'Where salesdate >= :salesdatefrom and salesdate <= :salesdateto');
MyQuery1.ParamByName('salesdatefrom').AsDateTime:= LdtDateFrom;
MyQuery1.ParamByName('salesdateto').AsDateTime:= LdtDateTo;
MyQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
MyQuery1.Close;
MyConnection1.Disconnect;
end;

end.


----------------------------------------------------------------------------

*.dfm

object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 168
ClientWidth = 415
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object DBGrid1: TDBGrid
Left = 0
Top = 0
Width = 393
Height = 129
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object Button1: TButton
Left = 48
Top = 135
Width = 145
Height = 25
Caption = 'Connect && Open Query'
TabOrder = 1
OnClick = Button1Click
end
object Button2: TButton
Left = 208
Top = 135
Width = 145
Height = 25
Caption = 'Disconnect && Close Query'
TabOrder = 2
OnClick = Button2Click
end
object MyQuery1: TMyQuery
Connection = MyConnection1
FetchAll = True
Left = 264
Top = 64
end
object DataSource1: TDataSource
DataSet = MyQuery1
Left = 296
Top = 64
end
object MyConnection1: TMyConnection
Left = 232
Top = 64
end
end

Use the TParam.AsDate property instead of TParam.AsDateTime.
It looks like this is a new behaviour in the MySQL 5.0.41 server.
For example:

You are right, this is MySQL 5.0.41 date comparison new behavior, please refer http://bugs.mysql.com/bug.php?id=28336.

This new behavior break our application report result. So. just wait MySQL to solve this problem.

Thank you.