要求写一个SQL:
如果给你一个时间,知道该时间的年,月(或者季度)
1、现在如何根据第几个月来得到这个月的时间:
如:年->2011,月->2月,应该要得到:2011-02-01至2011-02-28
2、现在如何根据第几个季度来得到这个季度的时间:
如:年->2011,季度->3季度,应该要得到:2011-07-01至2011-09-30
如果给你一个时间,知道该时间的年,月(或者季度)
1、现在如何根据第几个月来得到这个月的时间:
如:年->2011,月->2月,应该要得到:2011-02-01至2011-02-28
2、现在如何根据第几个季度来得到这个季度的时间:
如:年->2011,季度->3季度,应该要得到:2011-07-01至2011-09-30
from dual
CONNECT BY LEVEL <= (add_months(TO_DATE('2011'||'02','YYYYMM'),1) - TO_DATE('2011'||'02','YYYYMM'))
;SELECT TO_DATE('2011'||'02','YYYYMM') + LEVEL - 1
from dual
CONNECT BY LEVEL <= (add_months(TO_DATE('2011'||'02','YYYYMM'),1) - TO_DATE('2011'||'02','YYYYMM'))
;
select to_date('2011'||lpad(2,2,'0'),'YYYYMM')+rownum-1 from dual
connect by rownum<=(select to_date('2011'||lpad(2+1,2,'0'),'YYYYMM')-to_date('2011'||lpad(2,2,'0'),'YYYYMM') from dual)
输入1……
select to_date('2011'||lpad((1-1)*3+1,2,'0'),'YYYYMM')+rownum-1 from dual
connect by rownum<=(select to_date('2011'||lpad(1*3+1,2,'0'),'YYYYMM')-to_date('2011'||lpad((1-1)*3+1,2,'0'),'YYYYMM') from dual)
with a as
(SELECT level cnt, (TO_DATE('2011'||'02','YYYYMM') + LEVEL - 1) needdate
from dual
CONNECT BY LEVEL <= (add_months(TO_DATE('2011'||'02','YYYYMM'),1) - TO_DATE('2011'||'02','YYYYMM')))
select a.needdate from a where a.cnt = 1
union all
select a.needdate from
a, (select count(1) maxcnt from a)sub
where a.cnt = sub.maxcnt
结果:
2011/02/01 00:00:00
2011/02/28 00:00:00
这个是直接在pl/sql运行就可以么?
[code=SQL]
select max(sdate) m_date,min(sdate) n_date from (
select to_char(to_date('2012'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate
from dual
connect by rownum <= 366)
where to_char(to_date(sdate,'yyyy-mm-dd'),'WW')=28 m_date n_date
-------------------------------------
1 2012-07-14 2012-07-08[/code]
--根据年月获取起始日期
select max(sdate) m_date,min(sdate) n_date from (
select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate
from dual
connect by rownum <= 365)
where to_char(to_date(sdate,'yyyy-mm-dd'),'MM')=2 m_date n_date
-----------------------------------
1 2011-02-28 2011-02-01
--根据年和季度获取起始日期
select max(sdate) m_date,min(sdate) n_date from (
select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate
from dual
connect by rownum <= 365)
where to_char(to_date(sdate,'yyyy-mm-dd'),'Q')=3 m_date n_date
-----------------------------------
1 2011-09-30 2011-07-01
1、select TO_DATE('2011'||'02','YYYYMM'),last_day(TO_DATE('2011'||'02','YYYYMM')) from dual
2、select min(rq) ,max(rq) from
(select to_date('2011'||'01','yyyy-mm')+rownum-1 rq from dual connect by rownum
<= 365)
where to_char(rq,'q')='3'