Segmentation Fault: Core dumped..;-)

HOWTO: Calculate the number of business days given a start and end date

Posted : May 12, 2004 at 5:07 pm [America/Los_Angeles]

If there is any language which makes me feel at home when I am working on a logic involving dates, it’s Oracle’s PLSQL. I understand that Dates are first-class citizens in Oracle’s PLSQL language, but the lack of equally (if not more) powerful and yet easy to use APIs by default in other languages (Java comes to mind) is somewhat sad. Anyway, here are two small implementations of this logic - one using PLSQL and the other using Perl.

Problem statement:

Calculate the number of business days (or week days) given a start date and end date

Using PLSQL:
--------------
CREATE OR REPLACE FUNCTION num_business_days(start_date IN DATE, end_date IN DATE)
   RETURN NUMBER IS

busdays NUMBER := 0;
stDate DATE;
enDate DATE;

BEGIN

stDate := TRUNC(start_date);
enDate := TRUNC(end_date);

if enDate >= stDate
then
  – Get the absolute date range
  busdays := enDate - stDate
        – Now subtract the weekends

		–  this statement rounds the range to whole weeks (using
		–  TRUNC and determines the number of days in the range.
		–  then it divides by 7 to get the number of weeks, and
		–  multiplies by 2 to get the number of weekend days.
	 - ((TRUNC(enDate,‘D’)-TRUNC(stDate,‘D’))/7)*2
		– Add one to make the range inclusive

	 + 1;

  /* Adjust for ending date on a saturday */
  IF TO_CHAR(enDate,‘D’) = ‘7′ THEN
    busdays := busdays - 1;
  END IF;

  /* Adjust for starting date on a sunday */

  IF TO_CHAR(stDate,‘D’) = ‘1′ THEN
    busdays := busdays - 1;
  END IF;
else
   busdays := 0;
END IF;

  RETURN(busdays);

END;
/

In order to test it, you would connect to the nearest Oracle schema/instance, compile
the function given above and then do something like:
(sqlplus)>SELECT num_business_days(to_date(’05/12/2004′, ‘MM/DD/YYYY’),
            to_date(’10/01/2004′, ‘MM/DD/YYYY’), 1) FROM DUAL;

Using Perl:
------------

use Date::Calc qw(:all);

my $start_date = ‘05/12/2004′;

my $end_date = ‘10/01/2004′;

print “Number of business days = “, &getNumberOfBusinessDays($start_date, $end_date), “n”;

sub getNumberOfBusinessDays($start_date, $end_date) {

	my ($year1, $month1, $day1) = &Decode_Date_US($start_date);

	my ($year2, $month2, $day2) = &Decode_Date_US($end_date);

	#Get the absolute date range
	my $date_diff = &Delta_Days($year1, $month1, $day1, $year2, $month2, $day2);

	if($date_diff >= 0) {

		$busdays = $date_diff -
                        # Now subtract the weekends this statement rounds the 

                        # range to whole weeks multiplies by 2 to get the 
                        # number of weekend days.
                          (&Week_Number($year2,$month2,$day2) -
                          &Week_Number($year1,$month1,$day1))*2

			# Add one to make the range inclusive
			 + 1;
		# Adjust for ending date on a saturday		
		if(&Day_of_Week($year2, $month2, $day2) == 6) {

			$busdays = $busdays -  1;
		}

		# Adjust for starting date on a sunday

		if(&Day_of_Week($year1, $month1, $day1) == 7) {

			$busdays = $busdays - 1;
		}

                # Subtract the number of official holidays from the Country-Holidays table

                # (assuming that you have one)  The country code is passed for this purpose.
                # See Note below
	}
	else {
		$busdays = 0;

	}

	return $busdays;
}

It would be nice to see implementations in Python and Java to see how simple this would be and what (if any) external module/APIs were needed in order to simplify the solution.

- Anand

Category: Application Development