### Thread: Calculate working days with oracle function

1. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Apr 2008
Posts
61
Rep Power
10

#### Calculate working days with oracle function

Hi All

Could anyone help pls, how do i can make oracle function for the following vb6 function.

This function is used for calculating working days between two date.

****************************************

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt <= EndDate
' MsgBox Format(DateCnt, "ddd")
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates. '

If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Function

Thanks for any kind help.
Regards
Mchoud

2. +
Check out this Oracle tip.

Or, You may need to create a holiday_days table to hold the days that you take as holiday, then create a function that returns the working days as follows:

Code:
```--(Courtesy of Rod West):
CREATE OR REPLACE FUNCTION working_days (p_start_date DATE, p_end_date DATE)
RETURN NUMBER
IS
v_holidays     NUMBER;
v_start_date   DATE   := TRUNC (p_start_date);
v_end_date     DATE   := TRUNC (p_end_date);
BEGIN
IF v_end_date >= v_start_date
THEN
SELECT COUNT (holiday)
INTO v_holidays
FROM holidays_days
WHERE holiday BETWEEN v_start_date AND v_end_date;

RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
+   (  (  NEXT_DAY (v_end_date, 'MON')
- NEXT_DAY (v_start_date, 'MON')
)
/ 7
)
* 5
- GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
- v_holidays;
ELSE
RETURN NULL;
END IF;
END working_days;
/```
3. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Apr 2008
Posts
61
Rep Power
10

#### Thanks for the reply

This is very good and useful function.
That is beyond my expectation.

Thanks very much
4. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Aug 2013
Posts
1
Rep Power
0
The following given code is not full correct.
Code:
```--(Courtesy of Rod West):
CREATE OR REPLACE FUNCTION working_days (p_start_date DATE, p_end_date DATE)
RETURN NUMBER IS
v_holidays     NUMBER;
v_start_date   DATE   := TRUNC (p_start_date);    v_end_date     DATE   := TRUNC (p_end_date);
BEGIN
IF v_end_date >= v_start_date
THEN
SELECT COUNT (holiday)
INTO v_holidays
FROM holidays_days
WHERE holiday BETWEEN v_start_date AND v_end_date;        RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)              +   (  (  NEXT_DAY (v_end_date, 'MON')                      - NEXT_DAY (v_start_date, 'MON')                     )                   / 7                  )                * 5              - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)              - v_holidays;
ELSE       RETURN NULL;
END IF;
END working_days; /```
If a holiday is a weekend, it will subtract that date 2x. I have fixed this bug in the following code...

Code:
```-- (Courtesy of Marlon Christiaan):
create or replace
FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)
RETURN NUMBER IS
v_holidays     NUMBER;
v_start_date   DATE   := TRUNC (p_start_date);
v_end_date     DATE   := TRUNC (p_end_date);
BEGIN
IF v_end_date >= v_start_date    THEN
SELECT COUNT (*)
INTO v_holidays
FROM holidays
WHERE day BETWEEN v_start_date AND v_end_date
AND day NOT IN (
SELECT hol.day
FROM holidays hol
WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7));        RETURN
GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)              +   (  (  NEXT_DAY (v_end_date, 'MON')                      - NEXT_DAY (v_start_date, 'MON')                     )                   / 7                  )                * 5              - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)              - v_holidays;
ELSE       RETURN NULL;
END IF;
END calculate_business_days;```