oracle時(shí)間處理函數(shù)

TO DATE 格式 Day dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out ordinal twelfth Month mm number 03 mon abbreviated mar month spelled out march Year yy two digits 98 yyyy four digits 1998 24 小時(shí)格式下時(shí)間范圍為 0 00 00 23 59 59 12 小時(shí)格式下時(shí)間范圍為 1 00 00 12 59 59 1 日期和字符轉(zhuǎn)換函數(shù)用法 to date to char 2 select to char to date 222 J Jsp from dual 顯示 Two Hundred Twenty Two 3 求某天是星期幾 select to char to date 2002 08 26 yyyy mm dd day from dual 星期一 select to char to date 2002 08 26 yyyy mm dd day NLS DATE LANGUAGE American from dual monday 設(shè)置日期語言 ALTER SESSION SET NLS DATE LANGUAGE AMERICAN 也可以這樣 TO DATE 2002 08 26 YYYY mm dd NLS DATE LANGUAGE American 4 兩個(gè)日期間的天數(shù) select floor sysdate to date 20020405 yyyymmdd from dual 5 時(shí)間為 null 的用法 select id active date from table1 UNION select 1 TO DATE null from dual 注意要用 TO DATE null 6 a date between to date 20011201 yyyymmdd and to date 20011231 yyyymmdd 那么 12 月 31 號中午 12 點(diǎn)之后和 12 月 1 號的 12 點(diǎn)之前是不包含在這個(gè)范圍之內(nèi)的 所以 當(dāng)時(shí)間需要精確的時(shí)候 覺得 to char 還是必要的 7 日期格式?jīng)_突問題 輸入的格式要看你安裝的 ORACLE 字符集的類型 比如 US7ASCII date 格式的類型就是 01 Jan 01 alter system set NLS DATE LANGUAGE American alter session set NLS DATE LANGUAGE American 或者在 to date 中寫 select to char to date 2002 08 26 yyyy mm dd day NLS DATE LANGUAGE American from dual 注意我這只是舉了 NLS DATE LANGUAGE 當(dāng)然還有很多 可查看 select from nls session parameters select from V NLS PARAMETERS 8 select count from select rownum 1 rnum from all objects where rownum select sysdate to char sysdate hh from dual SYSDATE TO CHAR SYSDATE HH 2003 10 13 19 35 21 07 SQL select sysdate to char sysdate hh24 from dual SYSDATE TO CHAR SYSDATE HH24 2003 10 13 19 35 21 19 獲取年月日與此類似 13 年月日的處理 select older date newer date years months abs trunc newer date add months older date years 12 months days from select trunc months between newer date older date 12 YEARS mod trunc months between newer date older date 12 MONTHS newer date older date from select hiredate older date add months hiredate rownum rownum newer date from emp 14 處理月份天數(shù)不定的辦法 select to char add months last day sysdate 1 2 yyyymmdd last day sysdate from dual 16 找出今年的天數(shù) select add months trunc sysdate year 12 trunc sysdate year from dual 閏年的處理方法 to char last day to date 02 year mmyyyy dd 如果是 28 就不是閏年 17 yyyy 與 rrrr 的區(qū)別 YYYY99 TO C yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001 18 不同時(shí)區(qū)的處理 select to char NEW TIME sysdate GMT EST dd mm yyyy hh mi ss sysdate from dual 19 5 秒鐘一個(gè)間隔 Select TO DATE FLOOR TO CHAR sysdate SSSSS 300 300 SSSSS TO CHAR sysdate SSSSS from dual 2002 11 1 9 55 00 35786 SSSSS 表示 5 位秒數(shù) 20 一年的第幾天 select TO CHAR SYSDATE DDD sysdate from dual 310 2002 11 6 10 03 51 21 計(jì)算小時(shí) 分 秒 毫秒 select Days A TRUNC A 24 Hours TRUNC A 24 60 60 TRUNC A 24 Minutes TRUNC A 24 60 60 60 TRUNC A 24 60 Seconds TRUNC A 24 60 60 100 100 TRUNC A 24 60 60 mSeconds from select trunc sysdate Days sysdate trunc sysdate A from dual select from tabname order by decode mode FIFO 1 1 to char rq yyyymmddhh24miss floor date2 date1 365