date1是"时间/日期"类型1: adoquery1.SQL.Add('select * from table1 where date1='''+formatdatetime('yyyy-m-d',MonthCalendar1.Date)+'''');//报错,'标准表达式中数据类型不匹配。'
adoquery1.sql.text: select * from table1 where date1='2007-3-11'
2: adoquery1.SQL.Add('select * from table1 where date1='+formatdatetime('yyyy-m-d',MonthCalendar1.Date));//没报错,但查询不成功
adoquery1.sql.text: select * from table1 where date1=2007-3-11
3: adoquery1.SQL.Add('select * from table1 where date1 like'+''''+combobox2.text+'-'+combobox3.text+'%'+'''');//查询成功
adoquery1.sql.text: select * from table1 where date1 like'2007-3%'为什么第二条SQL查询不成功的?
将date1改成"文本"类型后,1、3成功,2报错
adoquery1.sql.text: select * from table1 where date1='2007-3-11'
2: adoquery1.SQL.Add('select * from table1 where date1='+formatdatetime('yyyy-m-d',MonthCalendar1.Date));//没报错,但查询不成功
adoquery1.sql.text: select * from table1 where date1=2007-3-11
3: adoquery1.SQL.Add('select * from table1 where date1 like'+''''+combobox2.text+'-'+combobox3.text+'%'+'''');//查询成功
adoquery1.sql.text: select * from table1 where date1 like'2007-3%'为什么第二条SQL查询不成功的?
将date1改成"文本"类型后,1、3成功,2报错
procedure Tfjzdcxf.Button1Click(Sender: TObject);
begin
with dataf.fjzdcxadoquery do
begin
close;
sql.Clear;
SQL.Add('select * from fjzd where');
SQL.Add('((sdata>=#' + FormatDateTime('yyyy-MM-dd', fjzdcxf.DateTimePicker1.Date) + '# and sdata<=#' + FormatDateTime('yyyy-MM-dd', fjzdcxf.DateTimePicker2.date) + '#) and fqrname like ''%' + fjzdcxf.cxedit.Text + '%'') order by sdata');
Open;
end;
end;
当date1字段是datetime类型的时候;传递参数2006-3-7进去,DBMS会认为后面是一个数字表达式,就是整型数据,其结果为2006-3-7=1996;在这种情况下,将对应date1字段的值全部转换为浮点数据类型后与1996对比;没有找到符合条件的记录;
而当你将date1的类型改为varchar等字段类型时,dbms在将‘2006-7-3'字段转换为浮点数时肯定会报错。
所以所有的时间格式都可以转为double型类处理:
SQL := select * from table1 where date1=' + FloatToStr(DateTimeToFloat(StrToDate('2007-3-11')));
所以所有的时间格式都可以转为double型类处理:
SQL := select * from table1 where date1=' + FloatToStr(StrToDate('2007-3-11'));
呵呵,前面太大意了