Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Sql add days to a date taking into account the weekend

Posted on 2011-10-10
Medium Priority
Last Modified: 2012-05-12

I'm trying to calculate a due date for an order based on a set lead days parameter.
Ideally 'd like to do this in a function.

I have an order creation date.
From that I want to add 5 working days to it.
However, if part or all of the weekend fall within the 5 days I want to be able to take that into account

So an order placed on Thursday would be due the follwing Thursday.
i.e 5 working days plus 2 non working days.

Can anyone help me with the syntax.
Question by:EWHTLC
  • 3
  • 2
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36942571
This has been discusse many times. Ere is a decent article

Author Comment

ID: 36943187
THanks for that.
It does calculate the number of working days between 2 dates.

However, I'd like to be able to add 5 working days to the startdate.
So I need to calculate the end date
LVL 39

Accepted Solution

Aaron Tomosky earned 1600 total points
ID: 36943273
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 36945966
You don't need a UDF for this.  All you need is to build a "Workdays" table from here to a reasonable number of years in the future, than your query becomes a trivial calculation.  The big advantage of this approach is that you can also now expand to include Holidays as well.  Let me know if you are interested.

Author Comment

ID: 36947485
All a big help
I did actually get round to using a udf to do it.
Seems to work a treat

I'm pleased to say it's not that differnt from the solution posted by aarontomosky.

Thanks Everyone

Author Closing Comment

ID: 36947487

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question