oracle日期類型資料查詢解決例子

oracle|解決|資料

create table worklog_A(
   man_bar            CHAR(8) NOT NULL,
   man_name           VARCHAR2(22) NOT NULL,
   man_dep            VARCHAR2(40) NOT NULL,
   log_time           Date NOT NULL
   status             VARCHAR2(40)
);
CREATE unique INDEX worklog_A_INDEX 
ON workman(man_bar,man_name);
comment on table worklog_A is
'登到日記表';

sql:='select man_bar as "考勤條碼",man_name as "姓名",log_time as "登記時間",status as "狀態",man_dep as "所屬部門" ';
sql:=sql+' from worklog_A where man_bar='+''''+s11+''''
          +' and (to_char(trunc(log_time,'+'''dd'''+')'+','+'''yyyy-mm-dd'''+') between '
          + ''''+GetDateStr(dat1.Date)+''''+' and '+''''+GetDateStr(dat2.Date)+'''' ;
sql:=sql+') order by log_time desc';
RunSQL(ADO_pall,sql,0);

function Tfrmain.GetDateStr(dat: TdateTime): string;
var
  Year, Month, Day:Word;
begin
  DecodeDate(dat,Year,Month,Day);
  if(month>=10) then
        result:= IntToStr(Year) + '-'+IntToStr(Month)
  else
        result:= IntToStr(Year)+ '-' +'0'+ IntToStr(Month) ;
  if(day>=10) then
      result:=result+ '-'+IntToStr(Day)
  else
     result:=result+ '-'+'0'+IntToStr(Day);
end;

              sql1:='insert into worklog_A values(';
              sql1:=sql1+''''+s1+''''+','+''''+s2+''''+','+''''+s3+''''+',';
              sql1:=sql1+'sysdate'+','+''''+KK+''''+')';
              RunSQL(ADO_kqlog,sql1,1);
              StatusBar1.SimpleText:='今天總計：'+inttostr(ADO_tm.RecordCount+1)+'  考勤' ;