October 19, 2005 at 11:24 pm
How do I calculate the DATE 7 working days after a specified Date?
We do not care about Public holidays, it's just the weekends we wish to exclude!
Example, I have a date of 13/10/2005. I need to add 7 working days after this date. This means that the date would be 24/10/2005.
I know there is enough examples on how to calculate the number of working days between 2 dates however, I need to add 7 working days from a specified date!
October 20, 2005 at 12:46 am
Not particuarly elegent, but it works.
set datefirst 7
DECLARE @DayOfWeek TINYINT
SET @DayOfWeek=datepart(dw,getdate())
select DATEADD(dd,CASE WHEN @DayOfWeek<5 THEN 9 WHEN @DayOfWeek=7 THEN 10 ELSE 11 END, GETDATE())
For today (Thurs 20th Oct) this returns 2005/10/31, week after next, monday.
It can be done all in one line if needed. I just broke it up for clarity sake.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2005 at 6:01 pm
October 24, 2005 at 12:26 am
No problem. What part don't you understand?
The case statement takes the day of the week (Sunday=1, Monday=2,....) and based on that decides how many days to add to get 7 working days. Sun-Wed require 9 days adding to get 7 working days ahead, because only one weekend is inbetween. SAt needs 10 days and thurs and fri need 11 adding, since two weekends will pass during the next 7 working days.
The SET DateFirst is just for a precaution, to ensure that sunday=day 1.
Is that any better?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply