Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Function for adding only business days

Author  Topic 

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-08 : 23:12:39
Hello Guys,
I need to write a code or use a function which should add 2 business days to submission date field and exclude weekdays and holidays.Is there any function which will automatically exclude holidays and weekdays and show me the nextdate.I would really appreciate your help.Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-09 : 06:38:55
Given that there is no way of telling what a holiday is then no. You also have to decide what you mean by a holiday - country....


CREATE FUNCTION fn_CalcBusinessDay
(
@date datetime , -- start date
@interval int , -- No of days, plus or minus
@country varchar(50)
)
returns datetime AS
begin

declare @daysAdded int ,
@newDate datetime

select @daysAdded = 0 ,
@newDate = @date

while @daysAdded < abs(@interval)
begin
if @interval > 0
begin
select @newDate = DateAdd(d, 1, @newDate)
end
else
begin
select @newDate = DateAdd(d, -1, @newDate)
end

if dbo.fn_IsHolidayDay(@country, @newDate) = 0 and dbo.fn_CQS_IsWeekend(@newDate) = 0
select @daysAdded = @daysAdded + 1
end

return @newDate

end


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-09 : 09:06:37
Hi,
Thanks for your help. I will try to execute your code from the office and let you know the output whether it worked for me or no? Thanks
Go to Top of Page
   

- Advertisement -