最简单的是使用
/* Formatted on 2009/12/02 16:01 (Formatter Plus v4.8.8) */
SELECTTO_CHAR(TO_DATE('2009-12-02','YYYY-MM-DD'),'WW')
FROMDUAL;
不过to_char()函数在计算一年中第几周是从该年的1月1日开始的。
以下SQL能得到正确的自然周数.
/* Formatted on 2009/12/02 15:31 (Formatter Plus v4.8.8) */
SELECTTO_CHAR
(DECODE
(SIGN
((TO_DATE('2009-12-02','YYYY-MM-DD')
+TO_NUMBER
(DECODE(TO_CHAR(TRUNC(TO_DATE('2009-12-02',
'YYYY-MM-DD'
),
'YYYY'
),
'D'
),
'1','8',
TO_CHAR(TRUNC(TO_DATE('2009-12-02',
'YYYY-MM-DD'
),
'YYYY'
),
'D'
)
)
)
-2
)
-LAST_DAY(TO_DATE('2009-12-02','YYYY-MM-DD'))
),
1,LAST_DAY(TO_DATE('2009-12-02','YYYY-MM-DD')),
(TO_DATE('2009-12-02','YYYY-MM-DD')
+TO_NUMBER(DECODE(TO_CHAR(TRUNC(TO_DATE('2009-12-02',
'YYYY-MM-DD'
),
'YYYY'
),
'D'
),
'1','8',
TO_CHAR(TRUNC(TO_DATE('2009-12-02',
'YYYY-MM-DD'
),
'YYYY'
),
'D'
)
)
)
-2
)
),
'WW'
)
FROMDUAL;