• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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
0
jamesmetcalf74
Asked:
jamesmetcalf74
5 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now