得到結果如下表: 表中NLS_DATE_FORMAT表示日期格式. PARAMETER VALUE ----------------------------------- ----------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.0.0
或者查詢V$NLS_PARAMETERS表, select * from V$NLS_PARAMETERS; 也有類似結果
SQL>select to_date('2004-11-12 12-07-32','yyyy-mm-dd hh24-mi-ss') value from dual; VALUE ------------------- 2004.11.12 12:07:32
SQL>select to_date('20041015') value from dual; VALUE ------------------- 2004.10.15 00:00:00
SQL>select to_date('20041315') value from dual; ERROR 位于第 1 行: ORA-01861: 文字與格式字符串不匹配
sysdate 當前日期和時間
SQL>select sysdate value from dual; VALUE ------------------- 2003.11.23 17:09:01
last_day 本月最后一天
SQL>select last_day(sysdate) value from dual; VALUE ------------------- 2003.11.30 17:08:17
add_months(d,n) 日期d后推n個月
SQL>select add_months(sysdate,2) value from dual; VALUE ------------------- 2005.01.23 17:10:21
next_day(d,day) 日期d之后的第一周中,指定的那天(指定星期的第幾天)是什么日期
SQL>select next_day(sysdate,1) value from dual; VALUE ------------------- 2004.11.28 17:38:55
[oracle/plsql]oracle日期處理完全版
日期處理完全版 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
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 設置日期語言 ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 也可以這樣 TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. 兩個日期間的天數 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. 時間為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點之后和12月1號的12點之前是不包含在這個范圍之內的。 所以，當時間需要精確的時候，覺得to_char還是必要的 7. 日期格式沖突問題 輸入的格式要看你安裝的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，當然還有很多， 可查看 select * from nls_session_parameters select * from V$NLS_PARAMETERS
8. select count(*) from ( select rownum-1 rnum from all_objects where rownum = to_date('2002-02-28','yyyy-mm-dd') - to_date('2002- 02-01','yyyy-mm-dd')+1 ) where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not in ( '1', '7' )
Monday-Sunday, for format code DAY Mon-Sun, for format code DY 1-7, for format code D
11 select to_char(sysdate,'hh:mi:ss') TIME from all_objects 注意：第一條記錄的TIME 與最后一行是一樣的 可以建立一個函數來處理這個問題 create or replace function sys_date return date is begin return sysdate; end;
select to_char(sys_date,'hh:mi:ss') from all_objects; 12. 獲得小時數
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer SQL> select sysdate ,to_char(sysdate,'hh') from dual;
