with adodataset1 do begin close; commandtext:='select * from 数据 where 日期>=#'+formatdatetime('yyyy-mm-dd',dtp1.date)+'# and 日期<=#'+formatdatetime('yyyy-mm-dd',dtp2.date)+'#'; open; end;
with adodataset1 do begin close; commandtext:='select * from 数据 where 日期>=#'+formatdatetime('yyyy-mm-dd',dtp1.date)+'# and 日期<=#'+formatdatetime('yyyy-mm-dd',dtp2.date)+'#'; open; end;
不行,查不出数据结果。我用的是adoquery
procedure TForm1.Button1Click(Sender: TObject); var sqlstr:string; begin sqlstr:='select count(*) as rs from 数据 where 日期>=#'+formatdatetime('yyyy-MM-dd',DateTimePicker1.Date )+'# and 日期<=# '+formatdatetime('yyyy-MM-dd',DateTimePicker2.Date )+'#'; adoquery1.Close ; adoquery1.SQL.Clear ; adoquery1.SQL.Add(sqlstr); adoquery1.Prepared ; adoquery1.Open; 比如查询2007-11-1 8:00:00 到2007-11-1 9:00:00之间的数据。
procedure TForm1.Button1Click(Sender: TObject); var str1,str2:string; begin with query1 do begin close; str1:=formatdatetime('yyyymmddhhmmss',dtp1.datetime); str2:=formatdatetime('yyyymmddhhmmss',dtp2.datetime); sql.text:='select * from machingordermaster where n_date>='''+str1+''''+ 'and n_date<='''+str2+''''; open; end; end;
由于Access不支持直接比较日期字符串,因而程序要转化为浮点类型的日期再比较,具体函数(Delphi)StrToDateTime,然后比较,如: 2007-9-12 9:13:37 -->39337.3844560185 2007-9-29 9:57:17 -->39354.4147800926 SQL语句: select * from Table where Field>=39337.3844560185 and Field<=39354.4147800926如果没有时间也可以加#比较: select * from JobRecord where BegTime>=#2007-9-12# and BegTime <=#2007-9-29# 不过这样查 2007-9-29 00:00:01就查不出来。
String strSQL; TDateTime strField; TDateTime strValue; ADOQuery1.Close; ADOQuery1.SQL.Clear; strField=DateTimePicker1.Date; strValue=DateTimePicker2.Date; strSQL='select * from 数据 where 日期 between #' + strField.DateString + '# and #' + strValue.DateString() + '#' ; ADOQuery1.SQL.Text = strSQL; ADOQuery1.Active=true;
procedure TForm1.Button1Click(Sender: TObject); var sqlstr:string; begin sqlstr:='select count(*) as rs from 数据 where 日期> =#'+formatdatetime(FormatDateTime('YYYY-MM-DD HH:MM:SS',DateTimePicker1.date)+'# and 日期 <=# '+formatdatetime('YYYY-MM-DD HH:MM:SS',DateTimePicker1.date)+'#'; adoquery1.Close ; adoquery1.SQL.Clear ; adoquery1.SQL.Add(sqlstr); adoquery1.Prepared ; adoquery1.Open;
where 时间 between #'+formatdatetime('yyyy-mm-dd HH::mm::ss',DateTimePicker1.DateTime)+'# and #'+formatdatetime('yyyy-mm-dd HH::mm::ss',DateTimePicker2.DateTime)+'#
刚刚回答了一个类似的,你试一下,应该没有问题. with adoquery1 do begin close; SQL.Text := ' select * from test where borndate>=:begintime and borndate<=:endtime'; Parameters.ParamByName('begintime').Value :=datetimePicker1.DateTime; Parameters.ParamByName('endtime').Value :=datetimePicker2.DateTime; Open; while not eof do begin Memo1.Lines.Add(fields[0].AsString+' '+Fields[1].AsString); Next; end; end;
begin
close;
commandtext:='select * from 数据 where 日期>=#'+formatdatetime('yyyy-mm-dd',dtp1.date)+'# and 日期<=#'+formatdatetime('yyyy-mm-dd',dtp2.date)+'#';
open;
end;
begin
close;
commandtext:='select * from 数据 where 日期>=#'+formatdatetime('yyyy-mm-dd',dtp1.date)+'# and 日期<=#'+formatdatetime('yyyy-mm-dd',dtp2.date)+'#';
open;
end;
var
sqlstr:string;
begin
sqlstr:='select count(*) as rs from 数据 where 日期>=#'+formatdatetime('yyyy-MM-dd',DateTimePicker1.Date )+'# and 日期<=# '+formatdatetime('yyyy-MM-dd',DateTimePicker2.Date )+'#';
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(sqlstr);
adoquery1.Prepared ;
adoquery1.Open;
比如查询2007-11-1 8:00:00 到2007-11-1 9:00:00之间的数据。
var
str1,str2:string;
begin
with query1 do
begin
close;
str1:=formatdatetime('yyyymmddhhmmss',dtp1.datetime);
str2:=formatdatetime('yyyymmddhhmmss',dtp2.datetime);
sql.text:='select * from machingordermaster where n_date>='''+str1+''''+
'and n_date<='''+str2+'''';
open;
end;
end;
2007-9-12 9:13:37 -->39337.3844560185
2007-9-29 9:57:17 -->39354.4147800926
SQL语句:
select * from Table where Field>=39337.3844560185 and Field<=39354.4147800926如果没有时间也可以加#比较:
select * from JobRecord where BegTime>=#2007-9-12# and BegTime <=#2007-9-29#
不过这样查 2007-9-29 00:00:01就查不出来。
TDateTime strField;
TDateTime strValue;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
strField=DateTimePicker1.Date;
strValue=DateTimePicker2.Date;
strSQL='select * from 数据 where 日期 between #'
+ strField.DateString
+ '# and #'
+ strValue.DateString() + '#' ;
ADOQuery1.SQL.Text = strSQL;
ADOQuery1.Active=true;
var
sqlstr:string;
begin
sqlstr:='select count(*) as rs from 数据 where 日期> =#'+formatdatetime(FormatDateTime('YYYY-MM-DD HH:MM:SS',DateTimePicker1.date)+'# and 日期 <=# '+formatdatetime('YYYY-MM-DD HH:MM:SS',DateTimePicker1.date)+'#';
adoquery1.Close ;
adoquery1.SQL.Clear ;
adoquery1.SQL.Add(sqlstr);
adoquery1.Prepared ;
adoquery1.Open;
with adoquery1 do
begin
close;
SQL.Text := ' select * from test where borndate>=:begintime and borndate<=:endtime';
Parameters.ParamByName('begintime').Value :=datetimePicker1.DateTime;
Parameters.ParamByName('endtime').Value :=datetimePicker2.DateTime;
Open;
while not eof do
begin
Memo1.Lines.Add(fields[0].AsString+' '+Fields[1].AsString);
Next;
end;
end;
kssj.date:=ksrq.date;
jssj.date:=jsrq.date;
;
Parameters.ParamByName('endtime').Value :=datetimePicker2.DateTime;
Parameters.ParamByName('endtime').Value :=datetimePicker3.Date
Parameters.ParamByName('endtime').Value :=datetimePicker4.DateTime