把它转成数字类型也可,TDatetime其就是Dword SQL.Text := Format('select * from clients where date>=%f', [now]); 数据库会自动将转成日期类型的
在SQL提交日期型字段的写法与后台数据库有关: Paradox:美国格式日期'mm/dd/yyyy' MS SQL Server 国际标准日期格式'yyyy-mm-dd'
打开sqlserver中enterprise manager用return all rows可以看到时间的格式。只要按格式转化即可,下列是datetime类型的sql: …… convert(char(10),CheckTime,20)>='+''''+formatdatetime('yyyy-mm-dd',DtBegin.DateTime)……求checktime字段中的日期〉datatimepicker dtbegin中选定的日期。
我一直对日期处理的做法分以下几步:1:用MaskEdit1 的 EDITMASK 设置格式‘!9999年99月99日;1;_’用于供用户输入日期。(当然也可以扩展为‘!9999年99月99日00时00分00秒;1;_’--这个用来接受DATATIME, 或‘!99时99分99秒;1;_’) 2。拥护输入后用函数将其整理成DELPHI认同的可以用 STRTODATATIME()或 STRTODATE()的合法字符串。函数为: //Date:生成及判断是否可以有合法的Date型字符串: function system_class.create_date(DateStr: Tstringlist; MaskEditText: string): boolean; VAR year,month,DAY,BeginStr:STRING; begin create_date:=true; BeginStr:=MaskEditText; year:=copy(BeginStr,1,pos('年',BeginStr)-1); BeginStr:=copy(BeginStr,pos('年',BeginStr)+2,length(BeginStr)); month:=copy(BeginStr,1,pos('月',BeginStr)-1); BeginStr:=copy(BeginStr,pos('月',BeginStr)+2,length(BeginStr)); DAY:=copy(BeginStr,1,pos('日',BeginStr)-1); if (length(trim(year))<>4) or (strtoint(trim(year))<2000) or (strtoint(trim(year))>2500) then begin showmessage('年数没有填写正确。'); //MaskEdit.SetFocus ; create_date:=false; exit; end; if trim(month)<>'' then begin if (strtoint(trim(month))<=0) or (strtoint(trim(month))>12) then begin showmessage('月份没有填写正确。'); //MaskEdit.SetFocus ; create_date:=false; exit; end; end else begin showmessage('月份没有填写正确。'); //MaskEdit.SetFocus ; create_date:=false; exit; end; if trim(DAY)<>'' then begin if (strtoint(trim(DAY))<=0) or (strtoint(trim(DAY))>31) then begin showmessage('日期没有填写正确。'); //MaskEdit.SetFocus ; create_date:=false; exit; end; end else begin showmessage('日期没有填写正确。'); //MaskEdit.SetFocus ; create_date:=false; exit; end; DateStr.Add(trim(year)+'-'+trim(month)+'-'+trim(day)); end; //DATATIME类型: function system_class.create_datetime(DateStr: Tstringlist; MaskEditText: string): boolean; VAR year,month,DAY,hour,minute,second,BeginStr:STRING; begin create_datetime:=true; BeginStr:=MaskEditText; year:=copy(BeginStr,1,pos('年',BeginStr)-1); BeginStr:=copy(BeginStr,pos('年',BeginStr)+2,length(BeginStr)); month:=copy(BeginStr,1,pos('月',BeginStr)-1); BeginStr:=copy(BeginStr,pos('月',BeginStr)+2,length(BeginStr)); DAY:=copy(BeginStr,1,pos('日',BeginStr)-1); BeginStr:=copy(BeginStr,pos('日',BeginStr)+2,length(BeginStr)); hour:=copy(BeginStr,1,pos('时',BeginStr)-1); BeginStr:=copy(BeginStr,pos('时',BeginStr)+2,length(BeginStr)); minute:=copy(BeginStr,1,pos('分',BeginStr)-1); BeginStr:=copy(BeginStr,pos('分',BeginStr)+2,length(BeginStr)); second:=copy(BeginStr,1,pos('秒',BeginStr)-1); if (length(trim(year))<>4) or (strtoint(trim(year))<2000) or (strtoint(trim(year))>2500) then begin application.MessageBox('年数没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; if trim(month)<>'' then begin if (strtoint(trim(month))<=0) or (strtoint(trim(month))>12) then begin application.MessageBox('月份没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; end else begin application.MessageBox('月份没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; if trim(DAY)<>'' then begin if (strtoint(trim(DAY))<=0) or (strtoint(trim(DAY))>31) then begin application.MessageBox('日期没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; end else begin application.MessageBox('日期没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; if trim(hour)<>'' then begin if (strtoint(trim(hour))<0) or (strtoint(trim(hour))>24) then begin application.MessageBox('小时没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; end else begin hour:='00'; end; if trim(minute)<>'' then begin if (strtoint(trim(minute))<0) or (strtoint(trim(minute))>59) then begin application.MessageBox('分钟没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; end else begin minute:='00'; end; if trim(second)<>'' then begin if (strtoint(trim(second))<0) or (strtoint(trim(second))>59) then begin application.MessageBox('小时没有填写正确。','提示',32); //MaskEdit.SetFocus ; create_datetime:=false; exit; end; end else begin second:='00'; end; DateStr.Add(trim(year)+'-'+trim(month)+'-'+trim(day)+' '+trim(hour)+':'+trim(minute)+':'+trim(second)); //showmessage(DateStr.Strings[0]); end; //TIME类型 function system_class.create_time(DateStr: Tstringlist; MaskEditText: string): boolean; VAR hour,minute,second,BeginStr:STRING; begin create_time:=true; BeginStr:=MaskEditText; hour:=copy(BeginStr,1,pos('时',BeginStr)-1); BeginStr:=copy(BeginStr,pos('时',BeginStr)+2,length(BeginStr)); minute:=copy(BeginStr,1,pos('分',BeginStr)-1); BeginStr:=copy(BeginStr,pos('分',BeginStr)+2,length(BeginStr)); second:=copy(BeginStr,1,pos('秒',BeginStr)-1); if trim(hour)<>'' then begin if (strtoint(trim(hour))<0) or (strtoint(trim(hour))>24) then begin showmessage('小时没有填写正确。'); //MaskEdit.SetFocus ; create_time:=false; exit; end; end else begin hour:='00'; end; if trim(minute)<>'' then begin if (strtoint(trim(minute))<0) or (strtoint(trim(minute))>59) then begin showmessage('分钟没有填写正确。'); //MaskEdit.SetFocus ; create_time:=false; exit; end; end else begin minute:='00'; end; if trim(second)<>'' then begin if (strtoint(trim(second))<0) or (strtoint(trim(second))>59) then begin showmessage('小时没有填写正确。'); //MaskEdit.SetFocus ; create_time:=false; exit; end; end else begin second:='00'; end; DateStr.Add(trim(hour)+':'+trim(minute)+':'+trim(second));end; 3。最后在程序中SEL语句中用参数的形式体现日期类型了变量:如: VAR SQLSTR:STRING; ThisStrLis:Tstringlist; BEGIN ThisStrLis:=Tstringlist.create(self); ThisStrLis.clear; try if create_date(ThisStrLis;YourMaskEditText)=true then begin SQLSTR:=‘SQLECT * FROM YourTableName where DATE=:DATE’ QUERY1。CLOSE; QUERY1。SQL。CLEAR; QUERY1。SQL。ADD(SQLSTR); QUERY1.PARAMSBYNEMR('DATE').ASDATE:=DateStr.string[0]; try QUERY1.open; exept //连接数据库失败的处理。 end; end else begin //用户填写日期不正确的处理。 end; finally ThisStrLis.free; end;
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('select *');
Query1.sql.Add('from clients');
query1.sql.add('where date_open>='+#39+'1-1-1980'+#39);
Query1.prepare;
Query1.open;
end;
ShortDateFormat:='YYYY/MM/DD';
这样在以后的代码里就可以采用'2001/12/22'这样的格式而不用考虑不同的客户端不同的日期格式了,在什么样的机器里都可以应用。
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('select *');
Query1.sql.Add('from clients');
query1.sql.add('where date_open>=:FromDate');
Query1.ParamByName('FromDate').AsDateTime:=Date;
Query1.prepare;
Query1.open;
end;
SQL.Text := Format('select * from clients where date>=%f', [now]);
数据库会自动将转成日期类型的
Paradox:美国格式日期'mm/dd/yyyy'
MS SQL Server 国际标准日期格式'yyyy-mm-dd'
…… convert(char(10),CheckTime,20)>='+''''+formatdatetime('yyyy-mm-dd',DtBegin.DateTime)……求checktime字段中的日期〉datatimepicker dtbegin中选定的日期。
或‘!99时99分99秒;1;_’)
2。拥护输入后用函数将其整理成DELPHI认同的可以用 STRTODATATIME()或 STRTODATE()的合法字符串。函数为:
//Date:生成及判断是否可以有合法的Date型字符串:
function system_class.create_date(DateStr: Tstringlist;
MaskEditText: string): boolean;
VAR year,month,DAY,BeginStr:STRING;
begin
create_date:=true;
BeginStr:=MaskEditText;
year:=copy(BeginStr,1,pos('年',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('年',BeginStr)+2,length(BeginStr));
month:=copy(BeginStr,1,pos('月',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('月',BeginStr)+2,length(BeginStr));
DAY:=copy(BeginStr,1,pos('日',BeginStr)-1);
if (length(trim(year))<>4) or (strtoint(trim(year))<2000) or (strtoint(trim(year))>2500) then
begin
showmessage('年数没有填写正确。');
//MaskEdit.SetFocus ;
create_date:=false;
exit;
end; if trim(month)<>'' then
begin
if (strtoint(trim(month))<=0) or (strtoint(trim(month))>12) then
begin
showmessage('月份没有填写正确。');
//MaskEdit.SetFocus ;
create_date:=false;
exit;
end;
end
else
begin
showmessage('月份没有填写正确。');
//MaskEdit.SetFocus ;
create_date:=false;
exit;
end;
if trim(DAY)<>'' then
begin
if (strtoint(trim(DAY))<=0) or (strtoint(trim(DAY))>31) then
begin
showmessage('日期没有填写正确。');
//MaskEdit.SetFocus ;
create_date:=false;
exit;
end;
end
else
begin
showmessage('日期没有填写正确。');
//MaskEdit.SetFocus ;
create_date:=false;
exit;
end;
DateStr.Add(trim(year)+'-'+trim(month)+'-'+trim(day));
end;
//DATATIME类型:
function system_class.create_datetime(DateStr: Tstringlist;
MaskEditText: string): boolean;
VAR year,month,DAY,hour,minute,second,BeginStr:STRING;
begin
create_datetime:=true;
BeginStr:=MaskEditText;
year:=copy(BeginStr,1,pos('年',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('年',BeginStr)+2,length(BeginStr));
month:=copy(BeginStr,1,pos('月',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('月',BeginStr)+2,length(BeginStr));
DAY:=copy(BeginStr,1,pos('日',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('日',BeginStr)+2,length(BeginStr));
hour:=copy(BeginStr,1,pos('时',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('时',BeginStr)+2,length(BeginStr));
minute:=copy(BeginStr,1,pos('分',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('分',BeginStr)+2,length(BeginStr));
second:=copy(BeginStr,1,pos('秒',BeginStr)-1);
if (length(trim(year))<>4) or (strtoint(trim(year))<2000) or (strtoint(trim(year))>2500) then
begin
application.MessageBox('年数没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end; if trim(month)<>'' then
begin
if (strtoint(trim(month))<=0) or (strtoint(trim(month))>12) then
begin
application.MessageBox('月份没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
end
else
begin
application.MessageBox('月份没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
if trim(DAY)<>'' then
begin
if (strtoint(trim(DAY))<=0) or (strtoint(trim(DAY))>31) then
begin
application.MessageBox('日期没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
end
else
begin
application.MessageBox('日期没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
if trim(hour)<>'' then
begin
if (strtoint(trim(hour))<0) or (strtoint(trim(hour))>24) then
begin
application.MessageBox('小时没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
end
else
begin
hour:='00';
end;
if trim(minute)<>'' then
begin
if (strtoint(trim(minute))<0) or (strtoint(trim(minute))>59) then
begin
application.MessageBox('分钟没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
end
else
begin
minute:='00';
end;
if trim(second)<>'' then
begin
if (strtoint(trim(second))<0) or (strtoint(trim(second))>59) then
begin
application.MessageBox('小时没有填写正确。','提示',32);
//MaskEdit.SetFocus ;
create_datetime:=false;
exit;
end;
end
else
begin
second:='00';
end;
DateStr.Add(trim(year)+'-'+trim(month)+'-'+trim(day)+' '+trim(hour)+':'+trim(minute)+':'+trim(second));
//showmessage(DateStr.Strings[0]);
end;
//TIME类型
function system_class.create_time(DateStr: Tstringlist;
MaskEditText: string): boolean;
VAR hour,minute,second,BeginStr:STRING;
begin
create_time:=true;
BeginStr:=MaskEditText;
hour:=copy(BeginStr,1,pos('时',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('时',BeginStr)+2,length(BeginStr));
minute:=copy(BeginStr,1,pos('分',BeginStr)-1);
BeginStr:=copy(BeginStr,pos('分',BeginStr)+2,length(BeginStr));
second:=copy(BeginStr,1,pos('秒',BeginStr)-1);
if trim(hour)<>'' then
begin
if (strtoint(trim(hour))<0) or (strtoint(trim(hour))>24) then
begin
showmessage('小时没有填写正确。');
//MaskEdit.SetFocus ;
create_time:=false;
exit;
end;
end
else
begin
hour:='00';
end;
if trim(minute)<>'' then
begin
if (strtoint(trim(minute))<0) or (strtoint(trim(minute))>59) then
begin
showmessage('分钟没有填写正确。');
//MaskEdit.SetFocus ;
create_time:=false;
exit;
end;
end
else
begin
minute:='00';
end;
if trim(second)<>'' then
begin
if (strtoint(trim(second))<0) or (strtoint(trim(second))>59) then
begin
showmessage('小时没有填写正确。');
//MaskEdit.SetFocus ;
create_time:=false;
exit;
end;
end
else
begin
second:='00';
end;
DateStr.Add(trim(hour)+':'+trim(minute)+':'+trim(second));end;
3。最后在程序中SEL语句中用参数的形式体现日期类型了变量:如:
VAR SQLSTR:STRING;
ThisStrLis:Tstringlist;
BEGIN
ThisStrLis:=Tstringlist.create(self);
ThisStrLis.clear;
try
if create_date(ThisStrLis;YourMaskEditText)=true then
begin
SQLSTR:=‘SQLECT * FROM YourTableName where DATE=:DATE’
QUERY1。CLOSE;
QUERY1。SQL。CLEAR;
QUERY1。SQL。ADD(SQLSTR);
QUERY1.PARAMSBYNEMR('DATE').ASDATE:=DateStr.string[0];
try
QUERY1.open;
exept
//连接数据库失败的处理。
end;
end
else
begin
//用户填写日期不正确的处理。
end;
finally
ThisStrLis.free;
end;
再用FormateDateTime转化为你想要的格式,多方便。
能简单,何必复杂。