Apply an sql function to update an sql table

[MJV57]

I have a function that determines the number of working days given a current date and end date.Im not sure how I could apply this to the values in an sql table to come up with the working days in che workingday column of the table given that the table already has the end date. Here is the function:

USE [ProjCoorTools]
GO
/****** Object: UserDefinedFunction [dbo].[GetBusinessDays] Script Date: 11/26/2009 14:08:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin

declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime

select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0

select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1

while @Cnt < @DaysBetween
begin

select @EvalDate = @StartDate + @Cnt

if (dbo.IsDateAHoliday(@EvalDate) = 0)
BEGIN
if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
END

select @Cnt = @Cnt + 1
end

return @BusinessDays
end

 

[bborissov]

Table has End Date, but did the table has START date?
If so, then just pass them as parameters to the function:

SELECT ....,
       dbo.GetBusinessDays(StartDateColumn, EndDateColumn) AS WorkingDays
FROM TheTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

update tablename set workingday =dbo.GetBusinessDays (getdate,tablename.enddate)

 

[MJV57]

pwise thanks but when I use Getdate or Getdate() i get errors as shown below. Any suggestions. Thanks
------------------------------------------------
UPDATE ShopLoadSummary
SET WorkDaysRemain = dbo.GetBusinessDays(Getdate, CURENDDATE)


Msg 207, Level 16, State 1, Line 2
Invalid column name 'Getdate'


-------------------------------------------

UPDATE ShopLoadSummary
SET WorkDaysRemain = dbo.GetBusinessDays(Getdate(), CURENDDATE)


Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

 

[bborissov]

what is the type of WorkDaysRemain?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

what dose this return

SELECT dbo.GetBusinessDays(getdate(), '12/31/9')

 

[MJV57]

The type of workdaysremain is int

 

[MJV57]

pwise note my start date is decimal (9,0)

 

[bborissov]

The you should CAST or CONVERT it to datetime

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.