Home » SQL & PL/SQL » SQL & PL/SQL » Date Calculation Excluding Weekends
Date Calculation Excluding Weekends [message #286362] Fri, 07 December 2007 03:55 Go to next message
muthaharora
Messages: 11
Registered: August 2007
Junior Member
Hi All,


We are facing an issue with calcualtion of date which should exclude the weekends.

Requirment: We have Evendate as input with which we need to add a varaible it may be between(1..5) and obtain the To Date,

So the Eventdate will be the From date and From date + variable is the To Date. As per our calculation,
if the resultant To Date, is Saturday or Sunday then these days should be excluded and next working day(Monday..Friday)
should be returned as the date for the To_date.

Input: From Date: 7/12/07 (Friday) To Date: From Date + 6(Variable)

The To date will be = 13/12/07 Thursday, since we have to exclude the saturday and Sunday(8th and 9th) the To date should add
to 2 more days it should be 15/12/07(Saturday) since it falls on saturday, it should be skippped, and the to_date should be
next working day that is 17/12/07(monday).

The output of To date what is expected as per our requirment is 17/12/07.


Could u please help in writing a query for the above.

Thanks and Regards
Re: Date Calculation Excluding Weekends [message #286367 is a reply to message #286362] Fri, 07 December 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom "Counting the number of business days between 2 dates"

Regards
Michel
Re: Date Calculation Excluding Weekends [message #286372 is a reply to message #286362] Fri, 07 December 2007 04:04 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A simple case construction will do the trick:
SQL> WITH yourtable AS
  2  (
  3    SELECT SYSDATE     even_dt FROM dual UNION ALL
  4    SELECT SYSDATE + 1 even_dt FROM dual UNION ALL
  5    SELECT SYSDATE + 2 even_dt FROM dual UNION ALL
  6    SELECT SYSDATE + 3 even_dt FROM dual UNION ALL
  7    SELECT SYSDATE + 4 even_dt FROM dual UNION ALL
  8    SELECT SYSDATE + 5 even_dt FROM dual UNION ALL
  9    SELECT SYSDATE + 6 even_dt FROM dual
 10  )
 11  SELECT even_dt
 12       , TO_CHAR(even_dt, 'Dy') Dy
 13       , CASE TO_CHAR(even_dt+5, 'Dy')
 14         WHEN 'Sat' THEN even_dt + 7
 15         WHEN 'Sun' THEN even_dt + 6
 16         ELSE  even_dt + 5
 17         END next_dt
 18      , CASE TO_CHAR(even_dt+5, 'Dy')
 19         WHEN 'Sat' THEN TO_CHAR(even_dt + 7, 'Dy')
 20         WHEN 'Sun' THEN TO_CHAR(even_dt + 6, 'Dy')
 21         ELSE  TO_CHAR(even_dt + 5,'Dy')
 22         END next_dy
 23  FROM   yourtable
 24  /

EVEN_DT    DY  NEXT_DT    NEX
---------- --- ---------- ---
07/12/2007 Fri 12/12/2007 Wed
08/12/2007 Sat 13/12/2007 Thu
09/12/2007 Sun 14/12/2007 Fri
10/12/2007 Mon 17/12/2007 Mon
11/12/2007 Tue 17/12/2007 Mon
12/12/2007 Wed 17/12/2007 Mon
13/12/2007 Thu 18/12/2007 Tue

7 rows selected.

MHE
Previous Topic: inserting a jpeg image in to a table.
Next Topic: pl/sql anonymous block
Goto Forum:
  


Current Time: Wed Dec 07 16:38:41 CST 2016

Total time taken to generate the page: 0.06031 seconds