Oracle Time-Date operations ____oracle

Oracle time and date operations
Sysdate+ (5/24/60/60) Delays 5 seconds based on system time
SYSDATE+5/24/60 delays 5 minutes based on system time
SYSDATE+5/24 delays 5 hours based on system time
SYSDATE+5 delays 5 days based on system time
Add_months (sysdate,-5) delayed May on a system-time basis
Add_months (SYSDATE,-5*12) Delayed 5 years on a system-time basis


Late last month: Select Last_day (Add_months (Sysdate,-1)) from dual;
Last second of the month: select Trunc (Add_months (sysdate,1), ' MM ')-1/24/60/60 from dual
Week Monday Date: Select Trunc (sysdate, ' Day ') +1 from dual


Number of days since the beginning of the year: select Ceil (Sysdate-trunc (Sysdate, "year") from dual;

Today is the first few weeks of this year: select To_char (sysdate, ' FMWW ') from dual
Today is the first few weeks of this month: SELECT to_char (sysdate, ' ww ')-To_char (TRUNC (sysdate, ' MM '), ' WW ') + 1 as "Weekofmon" from dual

Days of the Month
SELECT To_char (Last_day (sysdate), ' DD ') days from dual
Days of the Year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual
The date of next Monday
SELECT next_day (sysdate, ' Monday ') from dual


============================================

--Calculating weekday methods

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

--This assumes that the date is not with time, otherwise add trunc before all the dates.
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;

Cited here: http://www.itpub.net/showthread.php?s=1635506cd5f48b1bc3adbe4cde96f227&threadid=104060&perpage=15 &pagenumber=1