通过传递过来的 日期比如 2011-10-18 和已经定义好的 时段 如 17:00:00 23:59:59
在执行SQL时 怎么讲日期拼接
CREATE OR REPLACE PROCEDURE BACK_LIST(
thisTime in varchar2
)
AS
thistimess varchar2(60);
BEGIN
thistimess:=''''||thisTime||'''';
open cursor for
SELECT * FROM TESTS WHERE TIMES BETWEEN TO_DATE(thistimess,'yyyy-MM-dd hh24:MI:ss')
AND
TO_DATE(thistimess,'yyyy-MM-dd hh24:MI:ss')这样报年份超出范围
在执行SQL时 怎么讲日期拼接
CREATE OR REPLACE PROCEDURE BACK_LIST(
thisTime in varchar2
)
AS
thistimess varchar2(60);
BEGIN
thistimess:=''''||thisTime||'''';
open cursor for
SELECT * FROM TESTS WHERE TIMES BETWEEN TO_DATE(thistimess,'yyyy-MM-dd hh24:MI:ss')
AND
TO_DATE(thistimess,'yyyy-MM-dd hh24:MI:ss')这样报年份超出范围
to_char(字段,'yyyy-MM-dd hh24:MI:ss')
CREATE OR REPLACE PROCEDURE BACK_LIST(
channel in varchar2,
thisTime in varchar2,
c_cursor out mypag.cursorType
) as
thistimess varchar2(60);
thistimess1 varchar2(60);
timepoint1 varchar2(10);
thistimess2 varchar2(60);
timepoint2 varchar2(10);
channels varchar2(100);
begin
thistimess :=''''||thisTime||'''';
channels :=''''||channel||'''';
timepoint1:='17:00:00';
timepoint2:='23:59:59';
thistimess1 :=''''||thisTime||' '||timepoint1||'''';
thistimess2 :=''''||thisTime||' '||timepoint2||'''';
open c_cursor for
select sum(timelength) timelength,channelname,createtime from channelresult where createtime
between
to_date(thistiess1,'yyyy-MM-dd hh24:MI:ss')
and
to_date(thistiess2,'yyyy-MM-dd hh24:MI:ss') andchannelname=channels group by channelname,createtime ;
通过传进来的thistime 来拼接时间 如传进来时2011-10-19 就是查时间范围在
2011-10-19 :17:00:00 到 2011-10-19 23:59:59 这段时间的数据
但我调试这个存储过程的时候报错
完整的年份值必须介于-4713和+9999之间,且不为0
thistimess1 :=thisTime||' '||timepoint1;
SELECT to_date('2011-10-18'||' '||'17:00:00','yyyy-mm-dd hh24:mi:ss') FROM dual;
channelname=channels 这个传进来的参数该怎么设置呢 能写成
channelname='cctv' 在存储过程中支持写
select sum(timelength) timelength,channelname,createtime from channelresult where createtime
between
to_date(thistiess1,'yyyy-MM-dd hh24:MI:ss')
and
to_date(thistiess2,'yyyy-MM-dd hh24:MI:ss') andchannelname=channels group by channelname,createtime
UNION ALL
select sum(timelength) timelength,channelname,createtime from channelresult where createtime
between
to_date(thistiess1,'yyyy-MM-dd hh24:MI:ss')
and
to_date(thistiess2,'yyyy-MM-dd hh24:MI:ss') andchannelname=channels group by channelname,createtime吗?
Eg: select to_date('2000-10-11','YYYY-MM-dd');
create or replace package PKG_Report is
TYPE Result_Cur is REF CURSOR;
--请假明细表 可按部门号、工号、姓名、假别、状态、请假时间段等来查询
PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur);end;
create or replace package body PKG_Report IS
PROCEDURE SP_Rpt_Leave(p_Org_ID varchar2,p_Employee_No varchar2,p_Employee_Name varchar2,p_Type_No VARCHAR2,p_Flow_Status varchar2,p_Date_Begin DATE,p_Date_End DATE,p_Result out Result_Cur) IS
v_SQL VARCHAR2(4000);
v_Date_Begin VARCHAR2(20);
v_Date_End VARCHAR2(20);
BEGIN
v_SQL :='SELECT t4.org_id 部门编号,t4.org_name 部门名称,t2.employee_no 工号,t3.employee_name 姓名
,t5.type_name 假别,t2.time_begin 开始时间,t2.time_end 结束时间,t2.leave_hours 请假时长
,t2.leave_reason 请假原因,t1.form_no 请假单号,t1.update_user 申请人,t1.update_date 申请时间
,t1.flow_status 审核状态
FROM oa_att_leave t1
INNER JOIN oa_att_leave_d t2 ON t1.form_id=t2.form_id
INNER JOIN oa_hr_machi_employee t3 ON t3.employee_no=t2.employee_no
INNER JOIN oa_hr_org t4 ON t3.org_id=t4.org_id
INNER JOIN oa_att_leave_type t5 ON t5.type_no=t2.type_no
WHERE t3.org_id IN ('||p_Org_ID||')';
IF NVL(p_Employee_No,' ')<>' ' THEN
v_SQL := v_SQL || ' AND t2.employee_no like ''%'||nvl(p_Employee_No,'%')||'%''';
END IF;
IF NVL(p_Employee_Name,' ')<>' ' THEN
v_SQL := v_SQL || ' AND t2.employee_name like ''%'||nvl(p_Employee_Name,'%')||'%''';
END IF;
IF p_Type_No IS NOT NULL THEN
v_SQL := v_SQL || ' AND t2.type_no IN ('||p_Type_No||')';
END IF;
IF p_Flow_Status IS NOT NULL THEN
v_SQL := v_SQL || ' AND t1.flow_status IN ('||p_Flow_Status||')';
END IF;
IF p_Date_End IS NOT NULL THEN
v_Date_End :=to_char(p_Date_End,'yyyy-mm-dd');
v_SQL := v_SQL ||' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_End||'''';
IF p_Date_Begin IS NOT NULL THEN --查询该区间内所有请假的人
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| '''';
END IF;
ELSE --查询当前时间(即开始时间)所有请假的人
IF p_Date_Begin IS NOT NULL THEN
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')<='''|| v_Date_Begin|| '''';
v_Date_Begin :=to_char(p_Date_Begin,'yyyy-mm-dd');
v_SQL := v_SQL || ' AND to_char(t2.time_begin,''yyyy-mm-dd'')>='''|| v_Date_Begin|| '''';
END IF;
END IF;
v_SQL := v_SQL||' ORDER BY t4.sort_id'; open p_Result for v_SQL ;
END SP_Rpt_Leave;
end; --end package