最简单的是使用
/* Formatted on 2009/12/02 16:01 (Formatter Plus v4.8.8) */
SELECT TO_CHAR (TO_DATE ('2009-12-02', 'YYYY-MM-DD'), 'WW')
FROM DUAL;
不过to_char()函数在计算一年中第几周是从该年的1月1日开始的。
以下SQL 能得到正确的自然周数.
/* Formatted on 2009/12/02 15:31 (Formatter Plus v4.8.8) */
SELECT TO_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
)
),