date computation in sql

I have a sql statement that computes a day that is 90 days from a GETDATE    
I would like for the result to avoid being on a weekend or holiday.
any advice
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The normal solution is to built a calendar table with this information (day, isWeekend, isHoliday). And fill it according to your holiday rules.
0
Missus Miss_SellaneusCommented:
You can use a function such as DATENAME(dw,'09/23/2013') to get the name of the day of the week and use IIF to add 1 if "Sunday" or add 2 if "Saturday". Use a calendar table as ste5an suggested for holidays.

If it's a holiday, add 1, then apply the weekend rule. I suppose it's possible there would be a holiday Friday and Monday. In such a case you'd have to apply holiday rule, apply weekend rule, and apply holiday rule again.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's a calendar table for you to download and use.  If it helps you click on the green 'Was this article helpful?' button at the end.

SQL Server is set up to handle weekends using DATEPART(dw, your_date), but it isn't set up to handle custom holidays, so in order to have one state recognize Wayne Newton's birthday as a holiday, and another recognize St. McPaddy O'Flavo Day as another, you're going to have to build your own table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

UnifiedISCommented:
You need to decide whether to add days or subtract days when 90 hits the weekend.
So you'll need a case statement that checks the day of week at 90 days plus today
This one will add one day to Sunday and 2 to Saturday:
--7 = sat, 1 = sun
SELECT DATEADD(DAY, 90 + CASE DATEPART(dw, DATEADD(DAY, 90, GETDATE())) WHEN 1 THEN 1
            WHEN 7 THEN 2
            ELSE 0
      END, GETDATE())
0
Scott PletcherSenior DBACommented:
Avoiding weekends is easy, as it's a simple calc.  But stick with numeric, not string, calcs/comparions, and that do not depend on specific DATEFIRST or other date/language settings.

For holidays, you will of course need your own table with NonWorkDays in it.


SELECT
    DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, future_date) % 7 >= 5 --0=Mon;...;5=Sat;6=Sun.
        THEN 7 - DATEDIFF(DAY, 0, future_date) % 7 ELSE 0 END, future_date)
FROM (
    SELECT DATEADD(DAY, 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS future_date
) AS assign_alias_names
0
jamesmetcalf74Author Commented:
Thanks guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.