Workdays Package

Overview

This package contains a number of functions which manipulate date values with regard to “working days”.

For the purposes of this package working days are Monday to Friday inclusive, Saturday and Sunday are not working days. Users with a different week pattern will need to amend the code accordingly. In addition, different values of parameter NLS_TERRITORY can give rise to different values for the first day of the week (see the Oracle7 Server Reference Manual). This package expects Sunday to be day number 1. If your settings are different, change the value of constant day_adj as directed in the code.

No account is taken by this package of holidays, fixed or otherwise. If you need to deal with them, you’ll have to write the appropriate code.

All the functions defined here have the necessary “purity level” to be used in SQL SELECT and WHERE clauses as well as PL/SQL.

Function Definitions

add_workdays (date, days)
Adds a number of working days (positive or negative) to the specified date. If zero days are added to a Saturday or Sunday, the Monday following is returned. Thus add_workdays(mydate,0) will always be a working day.
workdays_between (from, to)
Calculates the number of working days between the specified dates. If “to” comes before “from”, this value will be negative. Note that if the dates include a time component, the returned value will include a fraction. TRUNC() the dates first if you don’t want this.
first_workday (date, unit)

Returns the first working day in the time unit containing the specified date (similar to the Oracle TRUNC and ROUND functions for dates). Possible units are:

Century:
CC, SSC, C or CENTURY
Year:
YYYY, SYYYY, YYY, YY, Y, YEAR, SYEAR or RR
ISO Year:
IYYY, IYY, IY or I
Quarter:
Q or QUARTER
Month:
MONTH, MON, MM, M or RM
Week:
WEEK, WW or W

None of these values are case sensitive.

last_workday (date, unit)
Returns the last working day in the time unit containing the specified date. Possible units are the same as listed above.
next_workday (date)
Returns the next working day after the specified date.
prev_workday (date)
Returns the working day previous to the specified date

Download

If the above has whetted your interest, you can download the package building script, orawork.zip (3,097 bytes). I hope you find it useful.

This code is made available for use in both private and commercial applications under a Creative Commons Licence.

Whilst every effort has been made to ensure these routines operate accurately and properly, no liability will be accepted for any problems they may cause on your system.

Leave a Comment