How to add 5 business days to all values of a date column?

First of all do not get confused between 5 days and 5 business days. There is a big difference between these two. 5 Business days means 5 working days(Not holidays). 5 days can be any day. It can be working day or holiday.

This seems to be very difficult question but, we will feel it very easy after knowing the answer. Please follow my clues below to qrite the query.

Clue 1: We need to keep three cases in mind while writing this query. 

  • Saturday
  • Sunday
  • Weekday

Clue 2: If it is Sunday:

  • Exclude 0 days + Add 5 days which are business days(Mon – Fri) — So if it is Sunday, add 5 days to the date.

Clue 3: If it is Saturday:

  • Exclude Sunday(1 day) + Add 5 days which are business days(Mon – Fri) — So if it is Saturday, add 6 days to the date.

Clue 4: If it is Any Weekday:

  • Exclude Saturday & Sunday which come in between(2 days) + Add 5 days which are business days — So if it is Weekday, add 7 days to the date.

I hope now you do not need my query. You can write the query yourself. But I am sharing my query as my gift to you :)

———————————————————————————————
USE [ADVENTUREWORKS2012]
GO
———————————————————————————————
— Add 5 Business days to the date.
———————————————————————————————
SELECT TOP 3 [ModifiedDate],

CASE WHEN DATENAME(WEEKDAY,[ModifiedDate]) = ‘Sunday’
          THEN DATEADD(dd,5,[ModifiedDate])

          WHEN DATENAME(WEEKDAY,[ModifiedDate]) = ‘Saturday’
          THEN DATEADD(dd,6,[ModifiedDate])

ELSE DATEADD(dd,7,[ModifiedDate])

END AS Add_5_Business_Days

FROM [Person].[PersonPhone]
———————————————————————————————

Result Set:

Add Business Days

Note: 

Business days means we need to exclude our holidays also. We can keep a where condition to the above query to filter that criteria.

Leave a Reply

Your email address will not be published. Required fields are marked *