很多時間我們需要用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周 .
為了得到自然周,我們需要為該日期加上該年的1月1日所在周被忽略掉的那些天數,然後再用TO_CHAR( )函數,就可以取得自然周了。 但當1月1日為週日時,用TO_DATE(' ', 'D')計算出它是該周的第1天,實際上這1天已是本年的第一周了,所以需要用一個DECODE將其置為第8天,然後再計算被忽略掉的天數,才能得到正確的結果。
所以,可以採用下面的SQL語句,仍然以2009年1月12日為例:
to_date('','D')
是取該日期為一週內的第幾天,從週日開始,周日為1.所以如果1月1日為周日,那麼1月2日就應該是第2周 .上面的SQL語句,當日期為2009年12月31日時,計算出的結果就是01,這是什麼原因呢?
因為我們為日期加上被忽略的天數時,可能造成年末的日期跨年,成為下一年的日期,這樣再用TO_CHAR( )函數,取得的周就成了01。所以我們需要判斷當日期跨年時就置為年末的最後一天,從而取得正確的周。
所以,最終採用下面的SQL語句,就能夠得到正確的自然周了,以2009年12月31日為例:
另外附上用於日期和時間的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 | 時區名 (小寫) |