Databases
StackOverflow2013
Postsdbo
POSQL Server function to add working days to a date
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server function to add working days to a date
    primarykey
    data
    text
    <p>I have seen a number of postings regarding how to create an SQL Server function that will add a given number of working days to a date. But, none of them calculate exactly the way I need. We have a function that is currently done in code but that I want to move to a native SQL Server function (for use by stored procedures and in queries). We give our customers 5 working days to collect cargo before we start assessing storage charges. The 5 working days exclude weekends and holidays (we have a table with the holiday dates). The trick here is that I need to get the date immediately after the 5 working days regardless of whether it is a weekend or holiday. So I need the function to return the last working date, NOT the first working day after. So, for example:</p> <pre><code>Oct 20th (Sat) plus 5 working days = Oct 26th (Fri) Oct 21st (Sun) plus 5 working days = Oct 26th (Fri) Oct 22nd (Mon) plus 5 working days = Oct 29th (Mon) May 19th (Sat) plus 5 working days with May 21st a holiday = May 28th </code></pre> <p>The 5 working days is the current allotment, but this may change in the future so the number of working days needs to be a parameter. Also, the function may be used over fairly large datasets so I would prefer doing this without loops. We are running SQL Server 2008.</p> <p>Edit: This is not a duplicate of "<a href="https://stackoverflow.com/questions/5471524">Add business days to date in SQL without loops</a>" as they want the ending date to be a working day. I want my ending date to be whatever date immediately follows the last grace day (ie: 5 working days Mon to Fri I want the Sat date returned, NOT the following Mon).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USCaynadian
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload