Oracle里时间的应用

;
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'''' )  

查找2002-02-28至2002-02-01间除星期一和七的天数  
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).  

9.  
select months_between(to_date(''''01-31-1999'''',''''MM-DD-YYYY''''),  
to_date(''''12-31-1998'''',''''MM-DD-YYYY'''')) "MONTHS" FROM DUAL;  
1  

select months_between(to_date(''''02-01-1999'''',''''MM-DD-YYYY''''),  
to_date(''''12-31-1998'''',''''MM-DD-YYYY'''')) "MONTHS" FROM DUAL;  

1.03225806451613  
10. Next_day的用法  
Next_day(date, day)  

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;  

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 olde