Friday 8 February 2013

Excluding weekends in the week

Subtract or Add '# of days' to date and with no weekends: 
Follow Below Any method we can Excluding Saturday & Sundays.
The requirement is to add or subtract a number to date and that would avoid weekends.
 
1)Subtract 5 days from date and avoid weekends(Excluding Sat&Sunday)
TIMESTAMPADD(SQL_TSI_DAY,(case when DAYOFWEEK(Time.Date)=7 then -5
when DAYOFWEEK(Time.Date)=1 then -6 else -7 end) ,Time.Date)
 
2)Add 5 days from date and avoid weekends:
TIMESTAMPADD(SQL_TSI_DAY, (case when DAYOFWEEK(Time.Date)=7 then 6
when DAYOFWEEK(Time.Date)=1 then 5 else 7 end) ,Time.Date)

Find Below Image:


 

No comments:

Post a Comment