Home » SQL & PL/SQL » SQL & PL/SQL » Date Calculation Excluding Weekends
Date Calculation Excluding Weekends Fri, 07 December 2007 03:55
 muthaharora Messages: 11Registered: 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.

Thanks and Regards
Re: Date Calculation Excluding Weekends [message #286367 is a reply to message #286362] Fri, 07 December 2007 03:58
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Regards
Michel
Re: Date Calculation Excluding Weekends [message #286372 is a reply to message #286362] Fri, 07 December 2007 04:04
 Maaher Messages: 7062Registered: 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: a simple job...ORA-06550 error Next Topic: multiple self join
Goto Forum:

Current Time: Mon Aug 21 10:56:28 CDT 2017

Total time taken to generate the page: 0.01659 seconds