I want to create an SQL function
for calculating the number of business/working days between two dates, similar to the NETWORKDAYS function for Microsoft Excel.
This SQL function should accept two input parameters such as start date and end date of type DATE and should return the number of business/working days in integer format.
How should I create this SQL function and run it on IBM i?
Using SQL we can create a user-defined function (UDF) for calculating the number of business/working dates between two dates, similar to the NETWORKDAYS function for Microsoft Excel.
Creating the SQL function:
We can execute the following SQL code to create our
SQL function
. You can either run the below scripts from theRun SQL script
option from IBM iAccess client solutions
(ACS) or on IBM i green screen, write the script within a source member of source type SQL at the locationlibname/srcpfname
and then useRUNSQLSTM SRCFILE(libname/srcpfname) SRCMBR(srcmbrname) COMMT(*NONE) DFTRDBCOL(libname) DBGVIEW(*SOURCE)
command to execute this script tocreate SQL function
.Explanation of the code:
Line 1 -2:
Create and replace function workingday
that accepts two parametersstartdate
andenddate
of type date.Line 3: The
SQL function returns an integer value
which is the number of business/working days.Line 4-5:
Language is SQL
and it isdeterministic
i.e. it returns the same value on the same input dates each time.Line 6: begin of the function
Line 7: Declare
workingday
variable of type integer and initialize with value 0.Line 8: Declare
tempdate
variable of type date.Line 9: set the value of
tempdate
asstartdate
.Line 10-16: Run the
while loop
until days oftempdate <= days of enddate
.Days() function
returns the integer representation of the date i.e. forJan 1, 0001(0001-01-01)
itreturns 1
, for(0001-01-02)
itreturns 2
and so on. Then we check iftempdate
is not the 6th and 7th day of the week usingDAYOFWEEK_ISO() function
then we set theworkingday
asworkingday + 1
i.e. increment by 1 and no increment whentempdate
is the 6th and 7th day of the week. Afterend if
; we increment thetempdate
by 1 i.e. increment the number of days extracted fromtempdate
and convert back to datedate(days(tempdate) + 1)
andwhile loop end
.Line 17: Return the
workingday
value.Line 18: end of function.
Important Point:
Once the function was created an object of the type
*SRVPGM
and attributeCLE
was created in the specified library.How to call SQL function from Run SQL script ACS or STRSQL session on IBM i:
the number of business/working days returned is 4. Since the 5th is Thursday and the 10th is Tuesday and there are total days in between these two dates are 6 and non-working days are 2 i.e. 7th and 8th (Saturday and Sunday) therefore working days are only 4.