sqlstr:='select * from 质检报告2 where 1=1';
.....
if chkph.Checked then
sqlstr:=sqlstr+' and 批号='''+trim(txtph.Text)+'''';
if chkzjname.Checked then
sqlstr:=sqlstr+' and 质检员='''+trim(txtzjname.Text)+'''';
if chkshname.Checked then
sqlstr:=sqlstr+' and 审核='''+trim(txtshname.Text)+'''';
if chkdate.Checked then
sqlstr:=sqlstr+' and (生产日期 between '+dtime1.date+' and '+dtime2.Date+')';adoquery1.close;
adoquery1.sql.clear;
adoquery1.SQL.Text:=sqlstr;
adoquery1.Open;
经过测验只有日期型条件有误,怎么改呢?
.....
if chkph.Checked then
sqlstr:=sqlstr+' and 批号='''+trim(txtph.Text)+'''';
if chkzjname.Checked then
sqlstr:=sqlstr+' and 质检员='''+trim(txtzjname.Text)+'''';
if chkshname.Checked then
sqlstr:=sqlstr+' and 审核='''+trim(txtshname.Text)+'''';
if chkdate.Checked then
sqlstr:=sqlstr+' and (生产日期 between '+dtime1.date+' and '+dtime2.Date+')';adoquery1.close;
adoquery1.sql.clear;
adoquery1.SQL.Text:=sqlstr;
adoquery1.Open;
经过测验只有日期型条件有误,怎么改呢?
.....
if chkph.Checked then
sqlstr:=sqlstr+' and 批号='''+trim(txtph.Text)+'''';
if chkzjname.Checked then
sqlstr:=sqlstr+' and 质检员='''+trim(txtzjname.Text)+'''';
if chkshname.Checked then
sqlstr:=sqlstr+' and 审核='''+trim(txtshname.Text)+'''';
if chkdate.Checked then
sqlstr:=sqlstr+' and (生产日期 between '+DateTimeToStr(dtime1.date)+' and '+DatetimeToStr(dtime2.Date)+')';adoquery1.close;
adoquery1.sql.clear;
adoquery1.SQL.Text:=sqlstr;
adoquery1.Open;
sqlstr:='select * from 质检报告2 where 1=1';
.....
if chkph.Checked then
sqlstr:=sqlstr+' and 批号='''+trim(txtph.Text)+'''';
if chkzjname.Checked then
sqlstr:=sqlstr+' and 质检员='''+trim(txtzjname.Text)+'''';
if chkshname.Checked then
sqlstr:=sqlstr+' and 审核='''+trim(txtshname.Text)+'''';
if chkdate.Checked then {日期也要作为字符串处理,所以要加上单引号}
sqlstr:=sqlstr+' and (生产日期 between '+quotedstr(DateTimeToStr(dtime1.date))
+' and '+quotedstr(DatetimeToStr(dtime2.Date))+')';adoquery1.close;
adoquery1.sql.clear;
adoquery1.SQL.Text:=sqlstr;
adoquery1.Open;
据我所知,sqlserver里的日期型未必是delphi转换出来那样的结果
最好用cast或者convert处理一下的
把日期直接转成字符串比较!在很多情况是行不通的!除非数据库在比较条件的时候会把字符和日期型转换后比较!不然结果将不正确。
我的意思是这样!
……
if chkdate.Checked then {日期也要作为字符串处理,所以要加上单引号}
sqlstr:=sqlstr+' and (生产日期 between :S_date and :E_date);
with adoquery1 do
begin
close;
sql.text:= sqlstr;
ParamByName('S_date').Value:= dtime1.date;
ParamByName('E_date').Value:= dtime2.date;
open;
end;或者
if chkdate.Checked then {日期也要作为字符串处理,所以要加上单引号}
sqlstr:=sqlstr+' and (生产日期 between '+FloatToStr(dtime1.date)+' and '+FloatToStr(dtime2.date)+');'
sqlstr:=sqlstr+' and (生产日期 between '''+DateTimeToStr(dtime1.date)+''' and '''+DateTimeToStr(dtime2.Date)+''')'; 如果是access,要加上#
sqlstr:=sqlstr+' and (生产日期 between #'+DateTimeToStr(dtime1.date)+'# and #'+DateTimeToStr(dtime2.Date)+'#)';
sqlstr:='select * from 质检报告2 where 1=1';
.....
if chkph.Checked then
sqlstr:=sqlstr+' and 批号='''+trim(txtph.Text)+'''';
if chkzjname.Checked then
sqlstr:=sqlstr+' and 质检员='''+trim(txtzjname.Text)+'''';
if chkshname.Checked then
sqlstr:=sqlstr+' and 审核='''+trim(txtshname.Text)+'''';
if chkdate.Checked then
sqlstr:=sqlstr+' and ( convert(char(8),生产日期,112) between '+Quotedstr(FormatdateTime('YYYYMMDD',dtime1.date))+
' and '+Quotedstr(formatdateTime('YYYYMMDD',dtime2.Date))+')'; adoquery1.close;
adoquery1.sql.clear;
adoquery1.SQL.Text:=sqlstr;
adoquery1.Open;
sql server 中可以直接使用
总结一下SQL语句中引号(')、quotedstr()、('')、format()在SQL语句中的用法以
及SQL语句中日期格式的表示(#)、('')
在Delphi中进行字符变量连接相加时单引号用('''),又引号用('''')表示
首先定义变量
var
AnInt:integer=123;//为了方便在此都给它们赋初值。虽然可能在引赋初值在某些情况下不对
AnIntStr:string='456';
AStr:string='abc';
AFieldName: string='字符型编号';
ATableName: string='YourTable';
ADate:Tdatetime=now;
Adoquery1:tadoquery;
1,Delphi语句
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 字符型编号='abc' and 整型编号=123';
等价于
adoquery1.sql.text:=
'select '+AFieldName+' from '+ATableName+' where '+AFieldName
+'='''+AStr+''' and 整型编号='+AnIntStr;
也等价于
adoquery1.sql.text:=
'select '+AFieldName+' from '+ATableName+' where '+AFieldName
+'='+QuotedStr(AStr)+' and 整型编号='+Inttostr(AnInt);传到数据库服务器为:
select 字符型编号 from YourTable where 字符型编号='abc' and 整型编号=1232,Delphi语句中日期表示
对于access数据库:
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段=#2003-12-01#';
等价于:
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段=#'+FormatDateTime('yyyy-MM-dd',now)+'#';
传到服务器为:
select 字符型编号 from YourTable where 日期型字段=#2003-12-01#对于MSSQL数据库:
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段='2003-12-01'';
等价于:
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段='''+FormatDateTime('yyyy-MM-dd',now)+'''';
也等价于:
等价于:
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段='+QuotedStr(FormatDateTime('yyyy-MM-dd',now));传到服务器为:
select 字符型编号 from YourTable where 日期型字段='2003-12-01'日期字段还可以这样表示
Delphi语句
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段>='+QuotedStr(FormatDateTime('yyyy-MM-dd',now))
+' and 日期型字段<='+QuotedStr(FormatDateTime('yyyy-MM-dd',now+1));//明天
等价于
adoquery1.sql.text:=
'select 字符型编号 from YourTable where 日期型字段 between '+QuotedStr(FormatDateTime('yyyy-MM-dd',now))
+' and '+QuotedStr(FormatDateTime('yyyy-MM-dd',now+1)); 2003-12-1 14:53:00
发表评语»»» 2003-12-6 17:37:26 2003-12-3 17:32:23 futhanlo 发表评论。
如果用
adoquery1.sql.add();
形式又如何操作?请用Insert语句示例
adoquery1.sql.add(' insert into '+AtableName);
adoquery1.sql.add(' ( '+AFieldName+')');
adoquery1.sql.add(' values( '+quotedstr(AStr)+')');
识别日期就要前后加#
sqlstr:=sqlstr+' and (生产日期 between ''+datetostr(dtime1.date)+'' and ''+datetostr(dtime2.Date)+'')';
这样就没问题了
sqlstr:=sqlstr+' and (生产日期 between '''+DateTimeToStr(int(dtime1.date))+''' and '''+DateTimeToStr(int(dtime2.Date))+''')';
或者
adoquery1.Parameters.ParamByName('S_date').Value:=int(dtime1.Date);
adoquery1.Parameters.ParamByName('E_date').Value:= int(dtime2.date);
.....包含其他格式,完成要求。