To_Date('2003-02-24 12:30:20','yyyy-mm-dd hh:mi:ss')
create or replace function
-- -- works in roughly the same way as sybase datsdiff
-- call would be eg. datediff('month',date1,date2)
-- p_what would be 'HOUR', 'DAY','MONTH' OR 'QUARTER','H','D','M','MI'
datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
l_result:=null;
if (upper(p_what) = 'HOUR' or upper(p_what) = 'H') then
l_result:=((p_d2-p_d1)*24);
end if;
if (upper(p_what) = 'MI') then
l_result:=((p_d2-p_d1)*1440);
end if;
if (upper(p_what) = 'DAY' or upper(p_what) = 'D') then
l_result:=(p_d2-p_d1);
end if;
if (upper(p_what) = 'MONTH' or upper(p_what) = 'M') then
l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
end if;
if (upper(p_what) = 'QUARTER') then
l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) - (floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));
end if;
l_result:=floor(l_result);
return l_result;
end;
/
create or replace function
getdate return date
as
l_result date;
begin
select sysdate into l_result from dual;
return sysdate;
end;
/
CREATE SEQUENCE XZFW_Consultation_ID_seq NOCACHE;
create table XZFW_Consultation (
ID integer constraint XZFW_Consultation_ID_seq primary key not null,
Name varchar2(10) not null,
Tel varchar2(50) null,
Email varchar2(50) null,
Org_id integer not null,
Title varchar2(100) null,
Content varchar2(3000) not null,
Writetime date not null,
Type varchar2(2) not null,
Flag varchar2(2) null,
pid integer null,
User_id integer null,
Same int default 0 null,
Yid integer null
);