User Defined Function

[niall29]

When I run the the line in red in Query Analyser it runs great and displays the proper date. but when I put it in my Function it error out saying there is an error near 1


CREATE FUNCTION dbo.Get_AdjHrs (@EmpID int, @Date datetime )
RETURNS numeric (5,2) AS
BEGIN
Declare @AdjT numeric(5,2)

Select @AdjT = STR(SUM(Hrs_Time / 60), 6, 2)

From Hr_Adjustments

Where
Person_id = @EmpID and Hr_Date >dateadd("MM", -1, dbo.firstofmonth(getdate()))

RETURN @AdjT

END

What I am trying to do is get a report starting from the 1st of the previous month.
but I cannot figure out what I am doing wrong

Any help would be greatly appreciated
Thanks in advance.

 

[mrdenny]

First relace the " with '. That might just be enough to break it. Functions are very picky about stuff like this.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[niall29]

I tried Replacing " with ' and it gave me an error saying
Error 1023 Invalid parameter 1 specified for dateadd

Also something I didnt say in my last post was when I use the line in my previous post and press "check syntax" it says it was successful then when I press apply it gives the same error
Error 1023 Invalid parameter 1 specified for dateadd

I am totally out of Ideas because it is successful when I press "check syntax

 

[mrdenny]

yeah, sorry. There should be no quotes with dateadd.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[donutman]

Don't use a UDF for that...it's very inefficient.
thread183-921333.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[niall29]

What would you suggest? I am always eager to learn new things. Although MrDenny did give me the answer that Stopeed my errors

 

[donutman]

I assume that you are using the UDF in a SELECT clause. The query will run fastest if you create a derived table of EmpID and the SUM that you want, then inner join that to the rowset that you are reporting. Another choice that would still be more efficient than using a UDF is to write a subquery for the value you want in the SELECT clause.
Do you need more help? If so, post the query that uses the UDF.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]