# 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.
###### Who is Participating?

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.

Senior 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
Commented:
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
Microsoft SQL Server Developer, Architect, and AuthorCommented:

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

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

Commented:
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
Senior 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 (