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;