Difference Between Two dates, Skip Weekends - SQL Server

Create Procedure SKIP_WEEKENDS AS

Two formulas are required, Date1 and Date2. Assign them the values of the two dates you want to compare. For example, Date1 is Promised_ship_date, Date2 is Actual_ship_date.

Date1 is the lower date. Date2 is greater. This formula does NOT account for date1 being greater than date2.

Declare @Date1 Datetime

Declare @Date2 Datetime

Select @Date1 = Orders.Prom_Ship_date

Select @Date2 = Orders.Actual_Ship_date

Select datediff(dd,@Date1,@date2),datepart(dd,@date1),datepart(dw,@date1)

-- if this is > 0:

select case when (((datediff(dd,@date1@date2)-(6-datepart(dw,@date1)))/7) *5 >0

then

(((datediff(dd,@date1,@date2)-(6-datepart(dw,@date1)))/7)*5) +(6-datepart(dw,@date1))+datepart(dw,@date2)-1

else -- cross only one weekend

case when datepart(dw,@date1)>datepart(dw,@date2) then

6-datepart(dw,@date1)+datepart(dw,@date2) - 1

else -- crosses no weekends

datediff(dd,@date1,@date2)

end

end

end

Note, See the Crystal Version of this formula for more explanations, omitted here.

Back to Mini Solve Products and other tips