比如2010年6月上旬,就把1號到10號的所有日期查詢出來。
2010/06/01
...
...
...
2010/06/10
2010/06/01
...
...
...
2010/06/10
解决方案 »
- 我打算装oracle客户端 请问如何做?
- 请教:PLSQL中判断一个%ROWTYPE结果集的记录条数最好怎么实现?急等
- 请教一下在oracle中用imp命令恢复数据库的问题
- 删除分区表中的所有数据
- 关于Oracle 9i的问题
- 多用户并发执行buffer cache该如何调优?
- 我现在就RMAN还有一些问题需要大家帮忙。
- 一个关于时间的问题!急!急!急!急!急!
- expdb 只报ORA-39006: internal error这个错误
- Oralce 时间问题
- 表一定要有主键吗?关系表也需要?............................。。。。。。。。。。。【标题要长】
- 关于oracle的日期字段查询的奇怪问题
CONNECT BY level <=
( last_day(to_date('2010-05-01','YYYY-MM-DD')) - to_date('2010-05-01','YYYY-MM-DD') + 1)-- 生成连续日期区间,用connect by 语句!
SELECT to_date('2010-05-01','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <= 10;
SELECT to_date('2010-05-11','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <= 10;
下旬
SELECT to_date('2010-05-21', 'YYYY-MM-DD') + level - 1 AS perDay
FROM dual
CONNECT BY level <=(last_day(to_date('2010-05-21', 'YYYY-MM-DD')) -
to_date('2010-05-21', 'YYYY-MM-DD') + 1)能不能將上中下三旬都同時考慮進去
SELECT to_date('2010-05-01','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <= 10;PERDAY
---------
01-MAY-10
02-MAY-10
03-MAY-10
04-MAY-10
05-MAY-10
06-MAY-10
07-MAY-10
08-MAY-10
09-MAY-10
10-MAY-10
(select trunc(sysdate, 'mm') +
trunc(to_number(to_char(sysdate, 'dd')) / 10) * 10 fd
from dual)
select fd
from first_day
union
select fd + 2
from first_day
union
select fd + 3
from first_day
union
select fd + 4
from first_day
union
select fd + 5
from first_day
union
select fd + 6
from first_day
union
select fd + 7
from first_day
union
select fd + 8
from first_day
union
select fd + 9
from first_day
where to_char(fd, 'mm') = to_char(fd + 9, 'mm')
union
select fd + 10
from first_day
where to_char(fd, 'mm') = to_char(fd + 10, 'mm')
union
select fd + 11
from first_day
where to_char(fd, 'mm') = to_char(fd + 11, 'mm')
i_year VARCHAR2, -- 年份
i_month VARCHAR2, -- 月份
i_period NUMBER, -- 1:代表上旬;2:代表中旬;3代表下旬
o_cur OUT SYS_REFCURSOR
)
is
v_sql VARCHAR2(4000);
v_fromDate DATE;
v_toDate DATE;
begin IF ( (i_year >= '1970' AND i_year <= '9999') AND (i_month >='01' AND i_month <= '12') AND (i_period = 1 OR i_period = 2 OR i_period = 3) )
THEN
BEGIN
v_fromDate := to_date( i_year||'-'||i_month||'-'||lpad(to_char(i_period*10-9),2,'0'),'YYYY-MM-DD');
IF i_period = 3 THEN
v_toDate := last_day(v_fromDate)+1;
ELSE
v_toDate := v_fromDate + 10;
END IF; v_sql := ' SELECT to_char(perDay,''YYYY-MM-DD'') AS perDay FROM ( SELECT :v_fromDate + level - 1 as perDay FROM dual CONNECT BY level <= ( :v_toDate - :v_fromDate) ) t';
OPEN o_cur FOR v_sql USING v_fromDate, v_toDate, v_fromDate;
END;
ELSE
OPEN o_cur FOR 'SELECT ''对不起,输入参数有误,请查证!'' AS errors FROM dual';
END IF;END;
/set serveroutput on;
var c_cur refcursor;
exec p_month_day('2009','02',3,:c_cur);
print c_cur;
-- 用存储过程:CREATE OR REPLACE PROCEDURE p_month_day(
i_year NUMBER, -- 年份
i_month NUMBER, -- 月份
i_period NUMBER, -- 1:代表上旬;2:代表中旬;3代表下旬
o_cur OUT SYS_REFCURSOR
)
is
v_sql VARCHAR2(4000);
v_fromDate DATE;
v_toDate DATE;
begin IF ( (i_year >= 1900 AND i_year <= 9999) AND (i_month >= 1 AND i_month <= 12) AND (i_period = 1 OR i_period = 2 OR i_period = 3) )
THEN
BEGIN
v_fromDate := to_date( to_char(i_year)||'-'||lpad(to_char(i_month),2,'0')||'-'||lpad(to_char(i_period*10-9),2,'0'),'YYYY-MM-DD');
IF i_period = 3 THEN
v_toDate := last_day(v_fromDate)+1;
ELSE
v_toDate := v_fromDate + 10;
END IF; v_sql := ' SELECT to_char(perDay,''YYYY-MM-DD'') AS perDay FROM ( SELECT :v_fromDate + level - 1 as perDay FROM dual CONNECT BY level <= ( :v_toDate - :v_fromDate) ) t';
OPEN o_cur FOR v_sql USING v_fromDate, v_toDate, v_fromDate;
END;
ELSE
OPEN o_cur FOR 'SELECT ''对不起,输入参数有误,请查证!'' AS errors FROM dual';
END IF;END;
/set serveroutput on;
var c_cur refcursor;
exec p_month_day(1900,2,3,:c_cur);
print c_cur;
select last_day(to_date('2010-06','YYYY-MM')) from dual;