ORACLE to_char() 函数获取自然周数

最简单的是使用

/* 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

)

),

'WW'

)

FROM DUAL;