查询当天是一年的第几周

前提:一年开始的第一天在周一到周日之间的任意一天都算作一周的话:

SELECT   CEIL (  (  TO_DATE ('20100929', 'yyyymmdd')
                  - TRUNC (TO_DATE ('20100929', 'yyyymmdd'), 'Y')
                  + (  7
                     - TO_CHAR (TRUNC (TO_DATE ('20100929', 'yyyymmdd'), 'Y'),
                                'DAY',
                                'NLS_DATE_LANGUAGE=''numeric date language'''
                               )
                    )
                 )
               / 7
              )
       + 1
  FROM DUAL;

===============================================

TO_CHAR (TRUNC (TO_DATE ('20100929', 'yyyymmdd'), 'Y'),
                                'DAY',
                                'NLS_DATE_LANGUAGE=''numeric date language'''
                               )
计算当年的第一天是周几,注意,这里使用了''numeric date language''' 1~7就分别表示周一到周日,(前提是数据库的'NLS_DATE_LANGUAGE为'SIMPLIFIED CHINESE')

================================================

 TRUNC (TO_DATE ('20100929', 'yyyymmdd'), 'Y')
                  + (  7
                     - TO_CHAR (TRUNC (TO_DATE ('20100929', 'yyyymmdd'), 'Y'),
                                'DAY',
                                'NLS_DATE_LANGUAGE=''numeric date language'''
                               )
                    )
                 )
计算一年中的第一个周末是哪天?

然后整个合起来就是输入的日期是一年的第多少个周了.结果是40

用oracle自带的函数计算

SELECT TO_CHAR (TO_DATE ('20100929', 'yyyymmdd'), 'ww')
  FROM DUAL;

结果是39

因为自带的还是认为周日是一个周的开始.而我们认为周一是一个周的开始.

 

另外自带的函数查询

SELECT NEXT_DAY (TO_DATE ('20100929', 'yyyymmdd'), 2)
  FROM DUAL
查看下周一是哪天.

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22861158/viewspace-675128/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22861158/viewspace-675128/