to_char取周數的算法

http://xsb.itpub.net/post/419/492128

“周數”的計算規則如下：

算法一：iw的算法為星期一至星期日算一周，且每年的第一個星期一為第一周

即：IW = Week of year (1-52 or 1-53) based on the ISO standard 

SQL函數的取值是：

SELECT to_char(SYSDATE, 'iw') FROM dual;

注意有特殊的地方：如果一年當中第52周别之後至當年的12月31日之間，還有大于或等于4天的話，則定為當年的第53周，否則剩餘這些天數被歸為下一年的第1周；如果在不足52周别的話，則以下一年的時間來補；

比如20090101是周四，今年第一個周一是20090105，結果是第一個周一已經算是第二周了!

程式處理起來很複雜！

算法二：ww的算法為每年的1月1日作為當年的第一周的第一天（不管當年的1月1日是星期幾）；

即：WW = Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

SQL函數的取值是：

SELECT to_char(SYSDATE, 'ww') FROM dual;

這個算法與國人習慣也不同！但算法很簡單，可以變通為我們習慣的自然周算法(第一周和最後一周可能不足7天)。

很多時間我們需要用Oracle提供的to_char()函數來取得某個日期是屬于今年的第幾周.例如:TO_CHAR(TO_DATE('20090112','YYYYMMDD'), 'WW') ,得到的結果是02,就是第2?可看看月曆上,2009年1月12日第三周的周一.為什麼會有這樣的問題呢?

由于Oracle在to_char()函數計算一年中的第幾周是從該年的1月1日開始,7天為1周來計算的,是以2009年的1月12日,就是第2周.

為了得到自然周，我們需要為該日期加上該年的１月１日所在周被忽略掉的那些天數，然後再用TO_CHAR( )函數，就可以取得自然周了。

但當１月１日為周日時，用TO_DATE(' ', 'D')計算出它是該周的第1天，實際上這１天已是本年的第一周了，是以需要用一個ＤＥＣＯＤＥ将其置為第８天，然後再計算被忽略掉的天數，才能得到正确的結果。 

是以，可以采用下面的ＳＱＬ語句，仍然以2009年1月12日為例：

SELECT to_char(to_date('20090112', 'YYYYMMDD') +

to_number(decode(to_char(trunc(to_date('20090112', 'YYYYMMDD'), 'YYYY'), 'D'),

'1',

'8',

to_char(trunc(to_date('20090112', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2,

'WW')

FROM dual;

P.S. to_date('','D')是取該日期為一周内的第幾天,從周日開始,周日為1.是以如果1月1日為周日,那麼1月2日就應該是第2周.

上面的SQL語句，當日期為2009年12月31日時,計算出的結果就是01，這是什麼原因呢？

因為我們為日期加上被忽略的天數時，可能造成年末的日期跨年，成為下一年的日期，這樣再用TO_CHAR( )函數，取得的周就成了０１。是以我們需要判斷當日期跨年時就置為年末的最後一天，進而取得正确的周。

是以，最終采用下面的SQL語句，就能夠得到正确的自然周了，以2009年12月31日為例：

SELECT to_char(decode(sign((to_date('20091231', 'YYYYMMDD') +

to_number(decode(to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'),

'1',

'8',

to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2) -

last_day(to_date('20091231', 'YYYYMMDD'))),

1,

last_day(to_date('20091231', 'YYYYMMDD')),

(to_date('20091231', 'YYYYMMDD') +

to_number(decode(to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'),

'1',

'8',

to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2)),

'WW')

FROM dual;

另外附上用于日期和時間的Format:

FORMAT 描述 

HH 一天的小時數 （01-12） 

HH12 一天的小時數 （01-12） 

HH24 一天的小時數 （00-23） 

MI 分鐘 （00-59） 

SS 秒 （00-59） 

MS 毫秒 (000-999) 

US 微秒 (000000-999999) 

SSSS 午夜後的秒 （0-86399） 

AM 或 A.M. 或 PM 或 P.M. 正午辨別（大寫） 

am 或 a.m. 或 pm 或 p.m. 正午辨別（小寫） 

Y,YYY 帶逗号的年（4 和更多位） 

YYYY 年（4和更多位） 

YYY 年的後三位 

YY 年的後兩位 

Y 年的最後一位 

IYYY ISO 年（4位或更多位） 

IYY ISO 年的最後 3 位 

IY ISO 年的最後 2 位 

I ISO 年的最後一位 

BC 或 B.C. 或 AD 或 A.D. 紀元辨別（大寫） 

bc 或 b.c. 或 ad 或 a.d. 紀元辨別（小寫） 

MONTH 全長大寫月份名（空白填充為9字元） 

Month 全長混合大小寫月份名（空白填充為9字元） 

month 全長小寫月份名（空白填充為9字元） 

MON 大寫縮寫月份名（3字元） 

Mon 縮寫混合大小寫月份名（3字元） 

mon 小寫縮寫月份名（3字元） 

MM 月份号（01-12） 

DAY 全長大寫日期名（空白填充為9字元） 

Day 全長混合大小寫日期名（空白填充為9字元） 

day 全長小寫日期名（空白填充為9字元） 

DY 縮寫大寫日期名（3字元） 

Dy 縮寫混合大小寫日期名（3字元） 

dy 縮寫小寫日期名（3字元） 

DDD 一年裡的日子（001-366） 

DD 一個月裡的日子（01-31） 

D 一周裡的日子（1-7；周日是1） 

W 一個月裡的周數（1-5）（第一周從該月第一天開始） 

WW 一年裡的周數（1-53）（第一周從該年的第一天開始） 

IW ISO 一年裡的周數（第一個星期四在第一周裡） 

CC 世紀（2 位） 

J 儒略日（自公元前4712年1月1日來的天數） 

Q 季度 

RM 羅馬數字的月份（I-XII；I=JAN）（大寫） 

rm 羅馬數字的月份（I-XII；I=JAN）（小寫） 

TZ 時區名 （大寫） 

tz 時區名 （小寫）