Programs Knowledge

2012-10-22

Oracle 關於日期時間

Oracle 關於日期時間

計算時間週期

1.Daily

to_date( to_char( sysdate, 'dd-mm-yy' ), 'dd-mm-yy' ) --當天時間0點0分

to_date( to_char( sysdate, 'dd-mm-yy' )||' 23:59:59', 'dd-mm-yy hh24:mi:ss' ) --當天時間23:59:59

2.Weekly

NEXT_DAY(to_date( to_char( sysdate, 'dd-mm-yy' ), 'dd-mm-yy' ) -7, 1) --當週的星期日0點0分，參數1表示星期日為一週的第一天, 也可以直接下'SUNDAY'為一週的第一天

NEXT_DAY(to_date( to_char( sysdate, 'dd-mm-yy' )||' 23:59:59', 'dd-mm-yy hh24:mi:ss' ) , 1) --當週的星期六的23:59:59

trunc(sysdate,'day') --本週第1天

trunc(sysdate,'iw')   --本週第2天

P.S 若發生ORA-01846 Not a vaild day of the week, 此時必須將一週的第一天參數改為英文或數字(看原本下的是數字或英文)

3.Monthly

trunc(sysdate,'month') --當月１號0點0分

to_date( '01-'||to_char( sysdate, 'mm-yyyy' ), 'dd-mm-yyyy' ) --當月１號0點0分

to_date( to_char( last_day(sysdate), 'dd-mm-yyyy' )||' 23:59:59', 'dd-mm-yy hh24:mi:ss' ) --當月最後一天的23:59:59

4.Monthly

trunc(sysdate,'q') --（本季的第一天）

5.Yearly

trunc(sysdate,'year')  --本年的第一天 0點0分

6.Others

trunc(sysdate,'hh24')  --小時的後面補零 2011-12-29 16:00:00

trunc(sysdate,'mi') --分鐘的後面補零 2011-12-29 16:24:00

其他日期與時間

1.取得系統日期

2.日期欄位轉字串格式

Output：2008/01/20 22:31:20

只取得年份

只取得月份

將字串轉成日期格式

日期格式轉日期格式：先將日期轉成字串再轉回日期

3. 取得英文格式之月份

APR

4.取星期幾

星期六

5.日期為在星期中的第幾天

7

6.取得下個月的今天 



select sysdate "Today",add_months(sysdate,1) "Next Month" from dual;

Today Next Month

2009/4/26 下午 02:17:07 2009/5/26 下午 02:17:07

M USER_VIEWS;

6.取得民國年

先取得西元年轉成 number減1911在和月日串一起....搞定....

一個月的第一天
代碼 (雙擊代碼複製到粘貼板)

sysdate 為數據庫服務器的當前系統時間。
to_char 是將日期型轉為字符型的函數。
to_date 是將字符型轉為日期型的函數，一般使用 yyyy-mm-dd hh24:mi:ss格式，當沒有指定時間部分時，則默認時間為 00:00:00
dual 表為sys用戶的表，這個表僅有一條記錄，可以用於計算一些表達式，如果有好事者用 sys 用戶登錄系統，然後在 dual 表增加了記錄的話，那麼系統99.999%不能使用了。為什麼使用的時候不用 sys.dual 格式呢，因為 sys 已經為 dual 表建立了所有用戶均可使用的別名。
一年的第一天
代碼 (雙擊代碼複製到粘貼板) 


    to_char(SYSDATE,'yyyy')||'-01-01','yyyy-mm-dd' ) 
FROM dual

季度的第一天
代碼 (雙擊代碼複製到粘貼板) 


    to_char(SYSDATE,'yyyy-')|| 
    lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')|| 
    '-01', 
    'yyyy-mm-dd') 
FROM dual

floor 為向下取整
lpad 為向左使用指定的字符擴充字符串，這個擴充字符串至2位，不足的補'0'。
當天的半夜

trunc 是將 sysdate 的時間部分截掉，即時間部分變成 00:00:00
Oracle中日期加減是按照天數進行的，所以 +1-1/24/60/60 使時間部分變成了 23:59:59。
Oracle 8i 中僅支持時間到秒，9i以上則支持到 1/100000000 秒。
上個月的最後一天
代碼 (雙擊代碼複製到粘貼板)

add_months 是月份加減函數。
last_day 是求該月份的最後一天的函數。
本年的最後一天
代碼 (雙擊代碼複製到粘貼板) 


    last_day(to_date(to_char(SYSDATE,'yyyy')||'-12-01','yyyy-mm-dd')) 
    )+1-1/24/60/60 
FROM dual

本月的最後一天
代碼 (雙擊代碼複製到粘貼板)

本月的第一個星期一
代碼 (雙擊代碼複製到粘貼板) 


    to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd'), 
    '星期一' 
    ) 
FROM dual

next_day 為計算從指定日期開始的第一個符合要求的日期，這裡的'星期一'將根據NLS_DATE_LANGUAGE的設置稍有不同。
去掉時分秒
代碼 (雙擊代碼複製到粘貼板)

顯示星期幾
代碼 (雙擊代碼複製到粘貼板)

取得某個月的天數
代碼 (雙擊代碼複製到粘貼板) 


    to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')+ 
    1 
FROM dual

判斷是否閏年
代碼 (雙擊代碼複製到粘貼板) 


    to_char(last_day(to_date(to_char(SYSDATE,'yyyy')||'-02-01','yyyy-mm-dd')),'dd'), 
    '28','平年','閏年' 
    ) 
FROM dual

一個季度多少天
代碼 (雙擊代碼複製到粘貼板) 


    to_char(SYSDATE,'yyyy-')|| 
    lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+3,2,'0')|| 
    '-01','yyyy-mm-dd' 
    ) 
    ) 
    -  
    to_date( 
    to_char(SYSDATE,'yyyy-')|| 
    lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')|| 
    '-01','yyyy-mm-dd') 
    +1 
FROM dual