Tag Archives: function

Oracle PL/SQL – Calculating Weekdays between two dates


Calculating total number of Week Days between two given dates is most common requirement. This article shows few methods to do this.

Calculating total number of Week Days between two given dates

————————————————————-

CREATE OR REPLACE FUNCTION TotWeekDays( FromDate DATE ,

ToDate DATE )

RETURN NUMBER IS

ToTalSunDays NUMBER := NEXT_DAY(ToDate – 7,’SUNDAY’) -

NEXT_DAY(FromDate – 1,’SUNDAY’) ;

ToTalSaturDays NUMBER := NEXT_DAY(ToDate – 7,’SATURDAY’) -

NEXT_DAY(FromDate – 1,’SATURDAY’) ;

BEGIN

RETURN (ToDate – FromDate – (TotalSundays+TotalSaturdays)/7 -1) ;

END ;

Executing this function in a pl/sql block…

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 BusinessDays NUMBER;

3 BEGIN

4 BusinessDays := TotWeekDays(’01-MAY-2001′,’31-MAY-2001′) ;

5 DBMS_OUTPUT.PUT_LINE(‘Total Business Days : ‘||TO_CHAR(BusinessDays) ) ;

6* END;

SQL> /

Total Business Days : 23

Aside

Hello Friends, Using this article, i wanted to give brief on how we can exclude some forms/ function as security in oracle applications. plz see i am trying to use two different way to get the same functionality. There are … Continue reading

Aside

Here there are very basic still sometime become critical to know concept if you working in Oracle Database related activities.  Q: What is an Oracle instance?Every running Oracle database is associated with an Oracle instance. When adatabase is started on … Continue reading

Aside

Hello Friends, many of my past work involve integrating oracle discoverer with oracle applications, here there is one of the ways to leverage both to integrate within applications.  How To Open A Worksheet Directly From Oracle Applications Menu ?   … Continue reading

Aside

Hello Friend, here is the long awaited and most demanding article on FNDLOAD. The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file.The loader reads a configuration file to determine … Continue reading

Aside

Hello Friends, here again i trying to share very smart and easy way to do very secure feature to run either particular request/ program or a request set or a certain request group using a menu option and attaching “standard … Continue reading