我想在程序中动态改变Tadodataset的commandtext,但对时间字段怎么都不行,清各位帮我看看。(数据库access2000)var
strSql : AnsiString;
begin
strSql := ' select * from T_HRDocuments where ';
strSql := strSql + ' T_HRD_MadeDate >= #'+ FormatDateTime('yyyy-MM-dd hh:mm:ss',self.dtp_hrd_make1.DateTime)+'# ';
strSql := strSql + ' and T_HRD_MadeDate <= #'+ FormatDateTime('yyyy-MM-dd hh:mm:ss',self.dtp_hrd_make2.DateTime)+'#';
try
data.adods_hrd.DisableControls;
if data.adods_hrd.Active then
data.adods_hrd.Close;
data.adods_hrd.CommandText := strSql;
data.adods_hrd.Open;
finally
data.adods_hrd.EnableControls;
end;
end;执行上述程序到data.adods_hrd.Open;时,提示“不正常地定义参数对象,提供了不一致或不完整地信息”
程序调试得到strSql为:
strSql: ' select * from T_HRDocuments where T_HRD_MadeDate >= #2003-04-16 00:00:00# and T_HRD_MadeDate <= #2003-07-16 23:59:59#'
将strSql中的sql语句拷到access中查询,可以得到正确结果。请问为什么呀?程序中sql语句该怎样写啊?
strSql : AnsiString;
begin
strSql := ' select * from T_HRDocuments where ';
strSql := strSql + ' T_HRD_MadeDate >= #'+ FormatDateTime('yyyy-MM-dd hh:mm:ss',self.dtp_hrd_make1.DateTime)+'# ';
strSql := strSql + ' and T_HRD_MadeDate <= #'+ FormatDateTime('yyyy-MM-dd hh:mm:ss',self.dtp_hrd_make2.DateTime)+'#';
try
data.adods_hrd.DisableControls;
if data.adods_hrd.Active then
data.adods_hrd.Close;
data.adods_hrd.CommandText := strSql;
data.adods_hrd.Open;
finally
data.adods_hrd.EnableControls;
end;
end;执行上述程序到data.adods_hrd.Open;时,提示“不正常地定义参数对象,提供了不一致或不完整地信息”
程序调试得到strSql为:
strSql: ' select * from T_HRDocuments where T_HRD_MadeDate >= #2003-04-16 00:00:00# and T_HRD_MadeDate <= #2003-07-16 23:59:59#'
将strSql中的sql语句拷到access中查询,可以得到正确结果。请问为什么呀?程序中sql语句该怎样写啊?
从上面的定义可以看到,formatdatetime返回的是一个字符串,这样你在程序中用这个字符串和数据库的时间类型相比当然会出错,直接拷贝这个串到acess里执行,它已经帮它翻译成时间,所以又正确
所以程序中应该 StrToDateTime(FormatDateTime(...));
var
strSql : AnsiString;
begin
strSql := ' select * from T_HRDocuments where ';
strSql := strSql + ' T_HRD_MadeDate >= #'+ FormatDateTime('yyyy-MM-dd', self.dtp_hrd_make1.DateTime)+'# ';
strSql := strSql + ' and T_HRD_MadeDate <= #'+ FormatDateTime('yyyy-MM-dd', self.dtp_hrd_make2.DateTime)+'#';
try
data.adods_hrd.DisableControls;
if data.adods_hrd.Active then
data.adods_hrd.Close;
data.adods_hrd.CommandText := strSql;
data.adods_hrd.Open;
finally
data.adods_hrd.EnableControls;
end;
end;只传日期就可以了,不知为什么?
也许他把冒号后面的当做参数了