求一oracle 語句－－列出某年某月某旬的所有天數

[f_cole]

比如2010年6月上旬，就把1號到10號的所有日期查詢出來。
2010/06/01
...
...
...
2010/06/10

[luoyoumou]

SELECT to_date('2010-05-01','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <=
( last_day(to_date('2010-05-01','YYYY-MM-DD')) - to_date('2010-05-01','YYYY-MM-DD') + 1)

-- 生成连续日期区间，用connect by 语句!

[ding138]

学习中，1楼，你的SQL执行出来只有2010-5-1号这一条数据啊

[f_cole]

luoyoumou，謝謝。根據您所寫的，上旬，中旬分別可以用
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)

能不能將上中下三旬都同時考慮進去

[ngx20080110]

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

[zhsq_java]

with first_day as
(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')

[zhsq_java]

我这个是不是太没有技术含量了？

[luoyoumou]

-- 用存储过程：

CREATE OR REPLACE PROCEDURE p_month_day( 
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;

[luoyoumou]

-- 用存储过程：

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;

[chenhuizhouhb]

主要的语句就是一个：
select last_day(to_date('2010-06','YYYY-MM')) from dual;