得到结果如下表: 表中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;
