Calculating dates excluding Saturdays and Sundays

By | November 21, 2012

What if you need to write a custom logic in CRM to determine expected end date of something? Like I open an opportunity on 1st January 2012 and it is expected to be closed in 7 working days. Close by date must be updated automatically through table level script. Seems to be pretty much straight forward work right? But it’s not. When you say working days we need to calculate Close by date excluding weekends as we don’t use standard holiday sets and working hour’s configurations here.
Here is how you can exclude those Saturdays and Sundays using simple select query.
Update Opportunity
set oppo_closeby=(SELECT case DATEPART(dw, oppo_opened)
when 6 then oppo_opened + 4
when 5 then oppo_opened + 4
else oppo_opened +2 end as CloseByDate)
where oppo_opportunityid = 6001