Oracleʱ¼äÈÕÆÚ²Ù×÷

Oracleʱ¼äÈÕÆÚ²Ù×÷
sysdate+(5/24/60/60) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ãë
sysdate+5/24/60 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5·ÖÖÓ
sysdate+5/24 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Сʱ
sysdate+5 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ìì
add_months(sysdate,-5) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5ÔÂ
add_months(sysdate,-5*12) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Äê
ÉÏÔÂÄ©µÄÈÕÆÚ£ºselect last_day(add_months(sysdate, -1)) from dual;
±¾ÔµÄ×îºóÒ»Ã룺select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual
±¾ÖÜÐÇÆÚÒ»µÄÈÕÆÚ£ºselect trunc(sysdate,'day')+1 from dual
Äê³õÖÁ½ñµÄÌìÊý£ºselect ceil(sysdate - trunc(sysdate, 'year')) from dual;
½ñÌìÊǽñÄêµÄµÚ¼¸ÖÜ :select to_char(sysdate,'fmww') from dual
½ñÌìÊDZ¾Ôµĵڼ¸ÖÜ:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" from dual
±¾ÔµÄÌìÊý
SELECT to_char(last_day(SYSDATE),'dd') days from dual
½ñÄêµÄÌìÊý
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
ϸöÐÇÆÚÒ»µÄÈÕÆÚ
SELECT Next_day(SYSDATE,'monday') from dual
£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½£½
--¼ÆË㹤×÷ÈÕ·½·¨
create table t(s date,e date);
alter session set nls_date_format = 'yyyy-mm-dd';
insert into t values('2003-03-01','2003-03-03');
insert into t values('2003-03-02','2003-03-03');
insert into t values('2003-03-07','2003-03-08');
insert into t values('2003-03-07','2003-03-09');
insert into t values('2003-03-05','2003-03-07');
insert into t values('2003-02-01','2003-03-31');
-- ÕâÀï¼Ù¶¨ÈÕÆÚ¶¼ÊDz»´øʱ¼äµÄ£¬·ñÔòÔÚËùÓÐÈÕÆÚÇ°¼Ótrunc¼´¿É¡£
select s,e,e-s+1 total_days,
trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')) work_days
from t;
-- drop table t;
Òý´Ë£ºhttp://www.itpub.net/showthread.php?s=1635506cd5f48b1bc3adbe4cde96f227&threadid=104060&perpage=15&pagenumber=1
================================================================================
Åжϵ±Ç°Ê±¼äÊÇÉÏÎçÏÂÎ绹ÊÇÍíÉÏ
SELECT CASE
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 6 AND 11 T