Passing parameter to UDF
-
I have a UDF that accepts two dates and figures the week days between them. My problem is when calling this from the report I can't pass a dynamic date. If use getdate it works fine but when I enter a @param it give me a syntax error.
This is the statement I am using:
SELECT dbo.ufn_WorkDays('3/1/2005', '3/30/2005') AS DayCount
This works fine, but if I try this:
SELECT dbo.ufn_WorkDays(@StartDate, @EndDate) AS DayCount
This gives me the syntax error.
Is it possible to do this? Or am I just not doing it correctly?
Thanks,
-
Can you post the whole code snippet you execute? it's impossible to find the problem for sure with this info.
-
If you could post the syntax error that gets throw that would help too.
Steve.
-
Sorry for the delay in getting back. Thought I had it worked out a different way.
Below you can see the screen shot of what I am trying to do and what is happening. I'm pretty sure it has to do with the @ parameters I am trying to pass. The next picture down shows the result when just passing the GetDate.
-
Sorry but we don't see anything here.. can you just post the code and err message?
-
Here is the function that I am calling. The error is just a box that says "Syntax Error or Access Violation"
CREATE FUNCTION [dbo].[ufn_WeekdaysBetween]
(@StartDate datetime, @EndDate Datetime)
RETURNS int
AS
BEGIN
DECLARE @Day1 int
DECLARE @Day2 int
SET @Day1 = Datepart(dw, @StartDate)
SET @Day2 = Datepart(dw, @EndDate)
DECLARE @dSunday int
DECLARE @dSaturday int
SET @dSaturday = datepart(dw, CONVERT(smalldatetime, '2000-01-01'))
SET @dSunday = @dSaturday + 1 IF @dSunday > 7
SET @dSunday = 1
DECLARE @WorkDays int
SET @WorkDays = DateDiff(d, @StartDate, @EndDate) - DateDiff(ww, @StartDate, @EndDate) * 2 + 1 - CASE
WHEN @Day1 = @dSunday THEN 1 ELSE 0 END - CASE
WHEN @Day2 = @dSaturday THEN 1 ELSE 0 END
RETURN @WorkDays
END
-
try running this... it works for me :
CREATE FUNCTION [dbo].[ufn_WeekdaysBetween]
(@StartDate datetime, @EndDate Datetime)
RETURNS int
AS
BEGIN
DECLARE @Day1 int
DECLARE @Day2 int
SET @Day1 = Datepart(dw, @StartDate)
SET @Day2 = Datepart(dw, @EndDate)
DECLARE @dSunday int
DECLARE @dSaturday int
SET @dSaturday = datepart(dw, CONVERT(smalldatetime, '2000-01-01'))
SET @dSunday = @dSaturday + 1 IF @dSunday > 7
SET @dSunday = 1
DECLARE @WorkDays int
SET @WorkDays = DateDiff(d, @StartDate, @EndDate) - DateDiff(ww, @StartDate, @EndDate) * 2 + 1 - CASE
WHEN @Day1 = @dSunday THEN 1 ELSE 0 END - CASE
WHEN @Day2 = @dSaturday THEN 1 ELSE 0 END
RETURN @WorkDays
END
GO
SELECT dbo.ufn_WeekdaysBetween('3/1/2005', '3/30/2005') AS DayCount
Declare @StartDate smalldatetime, @EndDate smalldatetime
set @StartDate = '3/1/2005'
set @EndDate = '3/30/2005'
SELECT dbo.ufn_WeekdaysBetween(@StartDate, @EndDate) AS DayCount
--drop function ufn_WeekdaysBetween
the problem probabely comes from the way you set the parameters
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply