Oracle中给定某个月,如何查出这个月的每一天?
例:给出2010-09查出结果:
2010-09 2010-09-01
2010-09 2010-09-02
2010-09 2010-09-03
2010-09 2010-09-04
2010-09 2010-09-05
2010-09 2010-09-06
2010-09 2010-09-07
……
2010-09 2010-09-30
例:给出2010-09查出结果:
2010-09 2010-09-01
2010-09 2010-09-02
2010-09 2010-09-03
2010-09 2010-09-04
2010-09 2010-09-05
2010-09 2010-09-06
2010-09 2010-09-07
……
2010-09 2010-09-30
SQL> select trunc(to_date('20100801','yyyymmdd'),'mm')+rownum-1 from all_objects t where trunc(to_date('20100801','yyyymmdd'),'mm')+rownum-1 <add_months(trunc(to_date('20100801','yyyymmdd'),'mm'),1);
TRUNC(TO_DATE('20100801','YYYY
------------------------------
2010-8-1
2010-8-2
2010-8-3
2010-8-4
2010-8-5
2010-8-6
2010-8-7
2010-8-8
2010-8-9
2010-8-10
2010-8-11
2010-8-12
2010-8-13
2010-8-14
2010-8-15
2010-8-16
2010-8-17
2010-8-18
2010-8-19
2010-8-20
TRUNC(TO_DATE('20100801','YYYY
------------------------------
2010-8-21
2010-8-22
2010-8-23
2010-8-24
2010-8-25
2010-8-26
2010-8-27
2010-8-28
2010-8-29
2010-8-30
2010-8-31
31 rows selected
SQL> select trunc(to_date('20100809','yyyymmdd'),'mm')+rownum-1 from all_objects t where trunc(to_date('20100809','yyyymmdd'),'mm')+rownum-1 <add_months(trunc(to_date('20100809','yyyymmdd'),'mm'),1);
TRUNC(TO_DATE('20100809','YYYY
------------------------------
2010-8-1
2010-8-2
2010-8-3
2010-8-4
2010-8-5
2010-8-6
2010-8-7
2010-8-8
2010-8-9
2010-8-10
2010-8-11
2010-8-12
2010-8-13
2010-8-14
2010-8-15
2010-8-16
2010-8-17
2010-8-18
2010-8-19
2010-8-20
TRUNC(TO_DATE('20100809','YYYY
------------------------------
2010-8-21
2010-8-22
2010-8-23
2010-8-24
2010-8-25
2010-8-26
2010-8-27
2010-8-28
2010-8-29
2010-8-30
2010-8-31
31 rows selected
SQL>
另一种用connect by 吧
select to_char(to_date('2010-09','yyyy-mm')+level,'yyyy-mm-dd') from dual
connect by level<= to_number(to_char(last_day(to_date('2010-09','yyyy-mm')),'dd'));
select day_id
from (
select to_date('&month_str','YYYYMM') + rownum -1 day_id
from dual
connect by rownum <=31
) t
where to_char(day_id,'YYYYMM') = '&month_str'
修改下:
select to_char(to_date('2010-09','yyyy-mm')+LEVEL-1,'yyyy-mm-dd') from dual
connect by level<= to_number(to_char(last_day(to_date('2010-09','yyyy-mm')),'dd'));