Oracle日期周详解以及周开始结束时间计算

1 ORACLE中周相关知识描述

1.1           日期格式化函数

TO_CHAR(X [,FORMAT])：将X按FORMAT格式转换成字符串。X是一个日期，FORMAT是一个规定了X采用何种格式转换的格式字符串，FORMAT与周相关的有W，WW，IW，D，FMWW。

W的含义是一个月的第几周。是按照ORACLE自定义的标准周来返回周数。

IW是ISO标准周，它的含义是ISO标准周以周别为“主线”，每年最多可以有53个周别，但是每年至少要包含52个周别；如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，否则剩余这些天数被归为下一年的第1周；如果在不足52周别的话，则以下一年的时间来补；每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天；比如：在Oracle中  2012年01月01号依然属于IW周别2011年的第52周的第7天。这个用到的比较多。

WW是ORACLE自定义的标准周，它的含义是每年的1月1日作为当年的第一周的第一天（不管当年的1月1日是星期几）；比如：2014年01年01是周三，在Oracle中被定义为2014年WW的第一周的第一天。一般很少用到。

D是返回当前日期是这个星期的第几天。是按照从周日到周六来进行计算的，这是要注意的地方。

FMWW该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。

1.2           日期时间运算函数

NEXT_DAY(X,Y)：用于计算X时间后第一个星期Y的时间。Y是一个字符串，表示用当前会话语言表示的一周中某一天的全称（如星期一、星期二等），也可以是数值。

TRUNC(X [,FORMAT])：截断日期,  FORMAT 中与周相关的有D，IW，WW，W，FMWW。

W的含义是一个月的第几周。是按照ORACLE自定义的标准周来返回的是一个周数。

IW是ISO标准周，返回是的当前日期所在周的周一。

WW是ORACLE自定义的标准周。返回ORACLE自定义标准周所在的周一。

D的含义是的返回当前星期的第一天。令人感到奇怪的是根据D返回的是当前星期的第一天是星期日。这点是我们要注意的地方。

FMWW该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。取周的开始时间时在跨年的时候与IW有些区别，比如2012年01年01使用FMWW的时候，周的开始时间是2012-01-01，使用IW时，周的开始时间是2011/12/26。

ROUND(X [,FORMAT])：日期的四舍五入FORMAT中与周相关的有DAY。按周一到周三和周四到周日四舍五入到最近的周日。

2 根据给定时间取一周的开始时间和结束时间

--取周的开始时间和结束时间

SELECT TRUNC ( TO_DATE ( '2014-07-18' , 'YYYY-MM-DD' ), 'IW' ) AS STARTDATE FROM DUAL ; --本周周一

SELECT TRUNC ( TO_DATE ( '2014-07-18' , 'YYYY-MM-DD' ), 'IW' ) + 6 AS ENDDATE FROM DUAL ; --本周周日

SELECT TRUNC ( TO_DATE ( '2014-07-18' , 'YYYY-MM-DD' ), 'IW' ) - 7 AS STARTDATE FROM DUAL ; ---上周周一  

SELECT TRUNC ( TO_DATE ( '2014-07-18' , 'YYYY-MM-DD' ), 'IW' ) - 1  AS ENDDATE FROM DUAL ; --上周周日

--取周的开始时间和结束时间
SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') AS STARTDATE FROM DUAL;--本周周一
SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') + 6 AS ENDDATE FROM DUAL;--本周周日

SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') - 7 AS STARTDATE FROM DUAL;---上周周一   
SELECT TRUNC(TO_DATE('2014-07-18','YYYY-MM-DD'),'IW') - 1  AS ENDDATE FROM DUAL;--上周周日

 

3 根据给定周数取一周的开始时间和结束时间

取自然周的开始时间和结束时间的难点就需要判断年初的那几天是属于本年的第一周，还是属于上一年的最后一周，根据IW自然周的定义，少于等于3天是本年的话，属于上一年的最后一周，大于等于4天属于本年的话，加上上年的最后几天，算成本年的第一周。

--按照周一到周日为一周算周的开始时间和结束时间（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' ), '1' , '7' , 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' ), '1' , '7' , 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

--按照周一到周日为一周算周的开始时间和结束时间（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'),'1','7',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'),'1','7',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

 

4 获取一年的最大周次

--获取一年中的最大周次（IW）中国日历自然周

WITH PARAMS AS ( SELECT '2014' AS NF FROM DUAL )

SELECT TO_CHAR ( TO_DATE ( PM . NF || '-12-28' , 'YYYY-MM-DD' ), 'IYYYIW' ) FROM DUAL LEFT JOIN PARAMS PM ON 1 = 1

--获取一年中的最大周次（IW）中国日历自然周
WITH PARAMS AS (SELECT '2014' AS NF FROM DUAL)
SELECT TO_CHAR(TO_DATE(PM.NF || '-12-28','YYYY-MM-DD'),'IYYYIW') FROM DUAL LEFT JOIN PARAMS PM ON 1=1

上述SQL通过卡每年的12月28号属于哪个周，也判断一年有多少个自然周。

5  特别应该注意的地方

取周别的时候最好把年份带上，第一因为周别是相对于哪年的第几周，第二因为当使用IW的时候对于一年的开始那几天和结束那几天可能会产生一样的周数，就分不清属于哪年的第一周。

 

 

1、日期比较时精确到日，可以使用 TRUNC(sysdate,'dd')函数。
函数支持格式有：yyyy MM  dd  hh Mi
可以用 select TRUNC(sysdate,'yyyy') from dual  看看结果是什么。

不要按下面的方式比较日期：
TO_DATE(TO_CHAR(LOGTIME,'YYYY-MM-DD'), 'YYYY-MM-DD') < TO_DATE(TO_CHAR(SYSDATE - $DAYNUM$,'YYYY-MM-DD'), 'YYYY-MM-DD')

 

2、trunc(d1[,c1])  返回日期d1所在期间(参数c1)的第一天日期

d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)

c1对应的参数表:

本周星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）

本月初日期：month或mon或mm或rm

本季日期：q

本年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度) 

本世纪初日期：cc或scc

【返回】：日期
select sysdate from dual --当时日期

select trunc(sysdate) from dual

select trunc(sysdate ,'DD') from dual --今天日期

select trunc(sysdate,'d')+7 from dual --本周星期日

select trunc(sysdate,'dy')+7 from dual  --本周星期日

select trunc(sysdate,'day')+7 from dual --本周星期日

select trunc(sysdate,'q') from dual--本季开始日期

select trunc(sysdate,'month') from dual --本月开始日期

select trunc(sysdate ,'mm') from dual --本月开始日期

select trunc(sysdate,'year') from dual  --本年开始日期

select trunc(sysdate ,'yyyy') from dual --本年开始日期

select trunc(sysdate ,'HH24') from dual --本小时开始时间

select trunc(sysdate ,'MI') from dual --本分钟开始时间

select trunc(sysdate ,'CC') from dual --本世纪开始时间

select trunc(LAST_DAY(sysdate),'dd') from dual --本月最后一天

 

3、round(10.2356,2)函数可以对数字按指定保留小数位数四舍五入，这个函数还可以对日期四舍五入
select round(sysdate,'yyyy') from dual 四舍五入到年

select round(sysdate,'mm') from dual 四舍五入到月

select round(sysdate,'dd') from dual 四舍五入到日

select round(sysdate,'hh') from dual 四舍五入到小时

select round(sysdate,'mi') from dual 四舍五入到分钟

 

4、TRUNC还可以对number类型使用，
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80