|
alter procedure SpBusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int)
as
begin
/*
Description:
Function designed to calculate the number of business days
between two dates.
*/
declare
@intWeeks int
,@indDays int
,@intSdays int
,@intEdays int
-- Find the number of weeks between the dates. Subtract 1
-- since we do not want to count the current week.
select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
print 'week'
print @intWeeks
-- calculate the number of days in these compelete week.
select @indDays = @intWeeks * @indDaysInWeek
print 'Est. Days'
print @indDays
-- Get the number of days in the starting week.
if @indDaysInWeek = 5
-- If Saturday, Sunday is holiday
if datepart( dw, @dtStartDate) = 7
select @intSdays = 7 - datepart( dw, @dtStartDate)
else
select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
else
-- If Sunday is only Holiday
select @intSdays = 7 - datepart( dw, @dtStartDate)
print 'Starting Days'
print @intSdays
-- Calculate the days in the last week. These are not included in the
-- week calculation. Since we are starting with the end date, we only
-- remove the Sunday (datepart=1) from the number of days. If the end
-- date is Saturday, correct for this.
if @indDaysInWeek = 5
if datepart( dw, @dtEndDate) = 7
select @intEdays = datepart( dw, @dtEndDate) - 2
else
select @intEdays = datepart( dw, @dtEndDate) - 1
else
select @intEdays = datepart( dw, @dtEndDate) - 1
print 'End Days'
print @intEdays
-- Sum everything together.
select @indDays = @indDays + @intSdays + @intEdays
if datepart( dw, @dtStartDate) <> 7 and @indDaysInWeek = 5
select @indDays = @indDays + 1
print 'Ans'
print @indDays
end
-- exec SpBusinessDays '05/15/2005','05/21/2005',6
/*
exec SpBusinessDays '05/13/2005','05/23/2005',5
select datepart( dw, '05/13/2005')
select datediff( mm, '05/18/2005', '05/31/2006')
*/
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
Currently he is working as Senior Soft. Engineer at
Cognizant Technology Solution.He is involved in various project activities like System Architecture, Design, and Development. He is fond of conduction training for various technologies. He has have worked on various language and platforms. He is Microsoft and Oracle Certified professional. He is spending quantity and quality time in .Net world. He had also spoiled his hand with java, too.
If work is not demanding, he spends good time with his wife, Purvi.He
blogs at WebDevs.com.