Sql add days to a date taking into account the weekend

Posted on 2011-10-10
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
    LVL 38

    Expert Comment

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

    Author Comment

    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 38

    Accepted Solution

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now