szSql:='select * from 油漆 where 进货日期>='''+datetimetoStr(dtpstart.Date) + '''';
szsql:=szsql+' ' +'and 进货日期<='''+datetimetostr(dtpend.date) + '''';
应该这样写
select * from 进货日期 between ('2002-05-10' and '2002-06-10')
你的错误是没把日期用单引号引起来
szsql:=szsql+' ' +'and 进货日期<='''+datetimetostr(dtpend.date) + '''';
应该这样写
select * from 进货日期 between ('2002-05-10' and '2002-06-10')
你的错误是没把日期用单引号引起来
query1.sql.clear;
query1.sql.add('select * from a.dbf where convert(char(10),进货日期,120) bwteen d1: and d2:);
query1.parambyname('d1'):=Datetimepicker1.date;
query1.parambyname('d2'):=Datetimepicker2.date;
query1.open;
;
szsql:=szsql+' ' +'and 进货日期<='+formatdatetime('yyyy-mm-dd',dtpstart.date)
先写一个函数:
function CreateGlobalTSQLString(ADateTime: TDateTime): string;
var
strDestDateTime: string;
begin
strDestDateTime := FormatDateTime('yyyy-mm-dd hh:mm:ss.zzz', ADateTime);
Result := 'CONVERT(DATETIME,''' + strDestDateTime + ''',20)';
end;然后在生成sql语句时这样写:
ADODataSet1.CommandText := 'SELECT * FROM 订单 WHERE 订购日期 BETWEEN '+
CreateGlobalTSQLString(DateTimePicker1.Date) + ' AND ' +
CreateGlobalTSQLString(DateTimePicker2.Date) + ' ORDER BY 订购日期';这样就不用考虑DateTimePicker的日期格式和库中日期字段的格式的不同了。实际上这样处理就是告诉sql server我给你传进去的日期是什么格式的,剩下的事就是sql server的事了。我只在sql server 2000上测试通过。有兴趣的朋友可以试试其它的数据库。
szsql:= szsql + ' and convert(char(10),'''+datetimetostr(dtpend.date)+''',120)';
paradox的时间格式必须是:月/天/年
eg: where date1>='05/19/2001' 分割符必须是/
Sql Server 到无所谓
也可以表示如下
var
sdt:string;
edt:string;
begin
sdt:= formatdatetime('yyyy-mm-dd',dtstart.Date);
edt:= formatdatetime('yyyy-mm-dd',dtpend.Date);
szSql:= 'select * from 油漆 where 进货日期 between convert(char(10),'''+sdt+''',120) and convert(char(10),'''+edt+''',120)';
end
我使用access.
szSql:= 'select * from 油漆 where convert(char(10),进货日期,120) between convert(char(10),'''+datetimetoStr(dtpstart.Date)+''',120)';
szsql:= szsql + ' and convert(char(10),'''+datetimetostr(dtpend.date)+''',120)';
DateTimePicker1.time:=0;
DateTimePicker2.time:=0;
我具体描述一下背景:
数据库为access2000,日期格式为:yyyy-mm-dd hh:mm:ss
datetimepicker控件中格式为yyyy-mm-dd 00:00:00
以上给为给得我基本都试过了,出现的问题归结为以下:
1.执行查询时报错为 表达式中'convert'函数未定义
2.执行时报告为对象格式不对
sdt:string;
edt:string;
begin
sdt:= formatdatetime('yyyy-mm-dd',dtstart.Date);
edt:= formatdatetime('yyyy-mm-dd',dtpend.Date);
szSql= Format('select * from 油漆 where 进货日期 between (''%s'' and ''%s'')', sdt, edt);
end
不用什么convert
szSql := Format('SELECT * FROM 油漆
WHERE CONVERT(char(10),进货日期,120)
BETWEEN CONVERT(char(10), %s),120)
AND CONVERT(char(10), %s,120)',
[DateTimeToStr(dtpstart.Date), DateTimeToStr(dtpend.date)]
);你说的 CONVERT 为定义的问题可能是 Access 不支持 CONVERT.
between('2002-5-1' and '2002-06-10')
这样她会说在between中没有发现 and操作符
dtpstart,dtpend中time设为00:00:00
between #2002-5-1# and #2002-06-10#
close;
sql.clear;
sql.add('select * from biao where rq1 >:d1 and rq2 <=:d2');
params[0].asdatetime:=datetimepicker1.date;
params[1].asdatetime:=dateTimepicker2.date;
open;end;
with dm.query1 do begin
close;
sql.clear;
sql.add('select * from biao where rq1 >:d1 and rq2 <=:d2');
params[0].asdatetime:=datetimepicker1.date;
params[1].asdatetime:=dateTimepicker2.date;
open;end;
query1.sql.clear;
query1.sql.add('select * from a.dbf where 进货日期 bwteen d1: and d2:);
query1.parambyname('d1'):=Datetimepicker1.date;
query1.parambyname('d2'):=Datetimepicker2.date;
query1.open;
query1.sql.clear;
query1.sql.add('select * from a.dbf where 进货日期 bwteen d1: and d2:);
query1.parambyname('d1'):=Datetimepicker1.date;
query1.parambyname('d2'):=Datetimepicker2.date;
query1.open;
query1.sql.clear;
query1.sql.add('select * from a.dbf where 进货日期 bwteen d1: and d2:);
query1.parambyname('d1').asdatetime:=Datetimepicker1.date;
query1.parambyname('d2').asdatetime:=Datetimepicker2.date;
query1.open;
query1.sql.clear;
query1.sql.add('select * from a.dbf where 进货日期 bwteen :d1 and :d2);
query1.parambyname('d1').asdatetime:=Datetimepicker1.date;
query1.parambyname('d2').asdatetime:=Datetimepicker2.date;
query1.open;
在ACCESS 应成如下格式:
procedure TForm1.Button1Click(Sender: TObject);
var
sql:string;
sdt:string;
edt:string;
begin
sdt:= formatdatetime('#yyy-mm-dd#',Dtp_start.DateTime);
edt:= formatdatetime('#yyy-mm-dd#',Dtp_end.DateTime) ;
sql := 'select * from leave_card where date between '+sdt+' and '+edt+'';
Query1.Close;
Query1.sql.Clear;
Query1.SQL.add(sql);
Query1.Open;
end;
你的SQL应为:
sdt:= formatdatetime('#yyy-mm-dd#',dtpstart.DateTime);
edt:= formatdatetime('#yyy-mm-dd#',dtpend.DateTime) ;
szSql:= 'select * from 油漆 where 进货日期 between '+sdt+' and '+edt+'';