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

2010-06-22 04:32:32 329 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

oracle求指定时间连续天数需求：得到指定时间开始往前数连续的天数，如id为a的数据对应时间：20200115，20200114，20200112，20200101，给定查询时间为20200115，则连续天数为2，一中断就终止初始数据：答案： -- 部分数据 WITH T_AG_TOP_SYMBOLS AS (SELECT '20191024' AS UPDATE_DATE, '000158' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '常山北明' AS SYMBOL_NA. 阅读详情

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

...全文

oracle心得--取住院实际天数 DECODE 函数等同于case when ,在这里配合TRUNC截断日期时间的函数是用来计算住院天数的，其目的是处理住院天数为0的情况。具体来说，当入院时间和出院时间在同一天时，住院天数应该被记为1天。阅读详情

f_cole

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

chenhuizhouhb 2010-06-22

打赏
举报

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

luoyoumou 2010-06-22

打赏
举报



-- 用存储过程：



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;

luoyoumou 2010-06-22

打赏
举报

-- 用存储过程：



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;

zhsq_java 2010-06-22

打赏
举报

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

zhsq_java 2010-06-22

打赏
举报

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')

ngx20080110 2010-06-22

打赏
举报



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

f_cole 2010-06-22

打赏
举报

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)

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

ding138 2010-06-22

打赏
举报

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

luoyoumou 2010-06-22

打赏
举报

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 语句!

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

相关推荐