--本周

select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,

          TO_CHAR(trunc(CREATE_DATE, 'd')+ 1,'yyyy-MM-dd')  as START_DATE ,

          TO_CHAR(trunc(CREATE_DATE, 'd')+ 7,'yyyy-MM-dd') as END_DATE

          from BG_PROJECT_INFO;

--本月

select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,

          TO_CHAR(trunc(CREATE_DATE, 'mm'),'yyyy-MM-dd')  as START_DATE ,

          TO_CHAR(last_day(trunc(CREATE_DATE, 'mm')),'yyyy-MM-dd') as END_DATE

          from BG_PROJECT_INFO;

--本季

select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,

          TO_CHAR(trunc(CREATE_DATE, 'Q'),'yyyy-MM-dd')  as START_DATE ,

          TO_CHAR(add_months(trunc(CREATE_DATE, 'Q'),3)-1,'yyyy-MM-dd') as END_DATE

          from BG_PROJECT_INFO;

--本年

select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,

          TO_CHAR(trunc(CREATE_DATE, 'yyyy'),'yyyy-MM-dd')  as START_DATE ,

          TO_CHAR(add_months(trunc(CREATE_DATE, 'yyyy'),12)-1,'yyyy-MM-dd') as END_DATE

          from BG_PROJECT_INFO;

select to_char(t.CREATE_DATE, 'YYYY-MM-DD') as 天,  t.PLAN_HOURS

from BG_PROJECT_INFO t

---按天统计

select to_char(t.CREATE_DATE+15/24, 'YYYY-MM-DD') as 天 ,sum(t.PLAN_HOURS) as 数量

from BG_PROJECT_INFO t

WHERE

    1=1

group by to_char(t.CREATE_DATE+15/24, 'YYYY-MM-DD')

ORDER by 天 NULLS  LAST;

--按周统计

select to_char(next_day(t.CREATE_DATE+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(t.PLAN_HOURS) as 数量

from BG_PROJECT_INFO t

WHERE

     1=1

group by to_char(next_day(t.CREATE_DATE+15/24 - 7,2),'YYYY-MM-DD')

ORDER BY 周;

--按月统计

select to_char(t.CREATE_DATE,'YYYY-MM') as 月份,sum(t.PLAN_HOURS) as 数量

from BG_PROJECT_INFO t

WHERE

      1=1

GROUP BY

    to_char(t.CREATE_DATE,'YYYY-MM')

ORDER BY 月份;

-- 按季统计

select to_char(t.CREATE_DATE,'q') 季度,sum(t.PLAN_HOURS) as 数量

from BG_PROJECT_INFO t

WHERE

   1=1

group by to_char(t.CREATE_DATE,'q')

ORDER BY 季度 NULLS  LAST;

--按年统计

select to_char(t.CREATE_DATE,'yyyy') AS 年度,sum(t.PLAN_HOURS) as 数量

from BG_PROJECT_INFO t

WHERE

    1=1

group by to_char(t.CREATE_DATE,'yyyy')

ORDER BY 年度;

--按照周一到周日为一周算周的开始时间和结束时间（IW）自然周

WITH PARAMS AS (SELECT TRUNC(TO_DATE('2009-01-01','YYYY-MM-DD'),'YYYY') AS SD FROM DUAL)

SELECT LEVEL 周次,

DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'','',TO_CHAR(PM.SD,'D'))),-1,

NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2))

 当周第一天,

DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'','',TO_CHAR(PM.SD,'D'))),-1,

NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) + 6

当周最后一天

FROM DUAL D

LEFT JOIN PARAMS PM ON 1=1

CONNECT BY LEVEL<=53

--按照周日到周六为一周算周的开始时间和结束时间（D）

SELECT LEVEL 周次,(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7  当周第一天,

    (TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7+6 当周最后一天

FROM DUAL CONNECT BY LEVEL<=53

--按照ORACLE标准（WW）

SELECT LEVEL 周次,TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7  当周第一天,

    TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7+

    DECODE((TO_CHAR(TO_DATE('2013-12-31','YYYY-MM-DD'),'DDD')-(LEVEL-1)*7),1,0,2,1,6) 当周最后一天

FROM DUAL CONNECT BY LEVEL<=53

WITH src

     AS (SELECT TRUNC (DATE '2018-08-01') fromdate,

                TRUNC (DATE '2019-09-01') todate

           FROM DUAL),

     dataset

     AS (    SELECT LEVEL AS seq

               FROM DUAL

         CONNECT BY LEVEL < (SELECT todate - fromdate FROM src)),

     datawithindices

     AS (SELECT src.*,

                dataset.*,

                CASE TO_CHAR (fromdate + seq - 1, 'DY')

                   WHEN 'SAT' THEN 1

                   WHEN 'SUN' THEN 2

                   WHEN 'MON' THEN 3

                   WHEN 'TUE' THEN 4

                   WHEN 'WED' THEN 5

                   WHEN 'THU' THEN 6

                   WHEN 'FRI' THEN 7

                END

                   indice

           FROM src, dataset),

     filt

     AS (SELECT seq, fromdate + seq - 1 nowaday, indice FROM datawithindices),

     alldata

     AS (SELECT filt.*, CEIL (indice / 7) + FLOOR (seq / 7) weekno FROM filt)

SELECT 'WEEK '||weekno week_seq, begindate, enddate

  FROM (SELECT weekno,

               FIRST_VALUE (nowaday)

                  OVER (PARTITION BY weekno ORDER BY weekno)

                  begindate,

               LAST_VALUE (nowaday)

                  OVER (PARTITION BY weekno ORDER BY weekno)

                  enddate,

               ROW_NUMBER () OVER (PARTITION BY weekno ORDER BY weekno) rn

          FROM alldata)

 WHERE rn = 1

SQL ----周

-----周

SELECT   NUMS ,

     (case when TO_DATE(WEEK_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else WEEK_START_DATE end)   as    WEEKS_START_DATE ,

     (case when TO_DATE(WEEK_END_DATE, 'yyyy-mm-dd') > TO_DATE('2018-09-15', 'yyyy-mm-dd') then '2018-09-15' else WEEK_END_DATE end)   as    WEEK_END_DATE 

 from (

SELECT  ROWNUM as NUMS,WEEK_START_DATE,WEEK_END_DATE from(

select  WEEK_START_DATE, WEEK_END_DATE from (

select

  ROWNUM as NUM,

  to_char(日期,'yyyy-mm-dd')   DATE_ID,

  to_char(日期,'yyyy')   YEAR_ID,

  to_char(日期,'q')   QUARTERID_ID,

  to_char(日期,'mm')   MONTH_ID,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_START_DATE ,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_END_DATE,

  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,

  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE,

  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'Q'),)-,'yyyy-MM-dd') as QUARTERID_END_DATE,

  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'yyyy'),)-,'yyyy-MM-dd') as YEAR_END_DATE

  from(

     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)   日期

     from   ALL_OBJECTS

     where   rownum<  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)<=to_date('2018-09-15','yyyy-mm-dd')

  )

) WHERE

      =

GROUP BY  WEEK_START_DATE,WEEK_END_DATE

ORDER BY WEEK_START_DATE

)

)

SQL ----月

-----月

SELECT   NUMS ,

     (case when TO_DATE(MONTH_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else MONTH_START_DATE end)   as    MONTH_START_DATE ,

     (case when TO_DATE(MONTH_END_DATE, 'yyyy-mm-dd') > TO_DATE('2018-09-15', 'yyyy-mm-dd') then '2018-09-15' else MONTH_END_DATE end)   as    MONTH_END_DATE 

 from (

SELECT  ROWNUM as NUMS,MONTH_START_DATE,MONTH_END_DATE from(

select  MONTH_START_DATE, MONTH_END_DATE from (

select

  ROWNUM as NUM,

  to_char(日期,'yyyy-mm-dd')   DATE_ID,

  to_char(日期,'yyyy')   YEAR_ID,

  to_char(日期,'q')   QUARTERID_ID,

  to_char(日期,'mm')   MONTH_ID,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_START_DATE ,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_END_DATE,

  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,

  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE,

  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'Q'),)-,'yyyy-MM-dd') as QUARTERID_END_DATE,

  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'yyyy'),)-,'yyyy-MM-dd') as YEAR_END_DATE

  from(

     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)   日期

     from   ALL_OBJECTS

     where   rownum<  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)<=to_date('2018-09-15','yyyy-mm-dd')

  )

) WHERE

      =

GROUP BY  MONTH_START_DATE,MONTH_END_DATE

ORDER BY MONTH_START_DATE

)

)

SQL ----季节

SELECT   NUMS ,

     (case when TO_DATE(QUARTERID_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else QUARTERID_START_DATE end)   as    QUARTERID_START_DATE ,

     (case when TO_DATE(QUARTERID_END_DATE, 'yyyy-mm-dd') > TO_DATE('2019-09-15', 'yyyy-mm-dd') then '2019-09-15' else QUARTERID_END_DATE end)   as    QUARTERID_END_DATE 

 from (

SELECT  ROWNUM as NUMS,QUARTERID_START_DATE,QUARTERID_END_DATE from(

select  QUARTERID_START_DATE, QUARTERID_END_DATE from (

select

  ROWNUM as NUM,

  to_char(日期,'yyyy-mm-dd')   DATE_ID,

  to_char(日期,'yyyy')   YEAR_ID,

  to_char(日期,'q')   QUARTERID_ID,

  to_char(日期,'mm')   MONTH_ID,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_START_DATE ,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_END_DATE,

  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,

  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE,

  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'Q'),)-,'yyyy-MM-dd') as QUARTERID_END_DATE,

  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'yyyy'),)-,'yyyy-MM-dd') as YEAR_END_DATE

  from(

     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)   日期

     from   ALL_OBJECTS

     where   rownum<  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)<=to_date('2019-09-15','yyyy-mm-dd')

  )

) WHERE

      =

GROUP BY  QUARTERID_START_DATE,QUARTERID_END_DATE

ORDER BY QUARTERID_START_DATE

)

)

SQL ----年

-----年

SELECT   NUMS ,

     (case when TO_DATE(YEAR_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else YEAR_START_DATE end)   as    YEAR_START_DATE ,

     (case when TO_DATE(YEAR_END_DATE, 'yyyy-mm-dd') > TO_DATE('2019-09-15', 'yyyy-mm-dd') then '2019-09-15' else YEAR_END_DATE end)   as    YEAR_END_DATE 

 from (

SELECT  ROWNUM as NUMS,YEAR_START_DATE,YEAR_END_DATE from(

select  YEAR_START_DATE, YEAR_END_DATE from (

select

  ROWNUM as NUM,

  to_char(日期,'yyyy-mm-dd')   DATE_ID,

  to_char(日期,'yyyy')   YEAR_ID,

  to_char(日期,'q')   QUARTERID_ID,

  to_char(日期,'mm')   MONTH_ID,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_START_DATE ,

  TO_CHAR(trunc(日期, 'd')+ ,'yyyy-MM-dd') as WEEK_END_DATE,

  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,

  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE,

  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'Q'),)-,'yyyy-MM-dd') as QUARTERID_END_DATE,

  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,

  TO_CHAR(add_months(trunc(日期, 'yyyy'),)-,'yyyy-MM-dd') as YEAR_END_DATE

  from(

     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)   日期

     from   ALL_OBJECTS

     where   rownum<  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-)<=to_date('2019-09-15','yyyy-mm-dd')

  )

) WHERE

      =

GROUP BY  YEAR_START_DATE,YEAR_END_DATE

ORDER BY YEAR_START_DATE

)

)

最新文章

  1. android deep link(深度链接)与自定义协议！
  2. 百度地图js根据经纬度定位和拖动定位点
  3. eclipse 文本编辑器
  4. Android Studio 单刷《第一行代码》系列 05 —— Fragment 基础
  5. （转载）四种常见的 POST 提交数据方式
  6. Windows 安装Django并创建第一个应用
  7. iOS-系统定位功能
  8. hql中in的用法
  9. python实现邮件发送
  10. 201521123067 《Java程序设计》第8周学习总结
  11. HDU2063-过山车-匈牙利算法
  12. [CQOI2014]危桥
  13. $Django 发送邮件--django封装模块和python内置SMTP模块
  14. ES6_入门（3）_顶层对象属性
  15. 2018SDIBT_国庆个人第六场
  16. c++11 并发 条件变量 超时等待的代码练习
  17. Java基础——iO（一）
  18. SVN中图标符号的含义
  19. 第五章 面向方面编程___OOP和AOP随想
  20. python控制台输出颜色

热门文章

  1. layer插件loading快速应用示例
  2. Ubuntu12.04+Caffe (+OpenCV+CPU-only)
  3. 【科创人&#183;独家】连续创业者高春辉的这六年：高强度投入打造全球领先的IP数据库
  4. 数据集：Introduction to Econometrics by Stock&amp;Watson
  5. Struts简单的实例
  6. Linux sed命令 -- 三剑客老二
  7. kubernetes之NFS动态提供Kubernetes后端存储卷
  8. .NET 树型递归
  9. SPOJ 1825 经过不超过K个黑点的树上最长路径 点分治
  10. seq2seq keras实现