March 5, 2007 at 4:06 am
Hello everyone, here is my function:::
CREATE FUNCTION GetTimeLeft
(
@EndTime datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime
SELECT @Result = datediff(dd, @EndTime,getdate())
RETURN @result
END
in this i used a getdate(), and i m shocked when
its give error " Invalid use of getdate within a function" , i also try this with dbo.getdate() but nothing happens.
Tell me y its not allowing to write getdate() in the functions??
as we used sme other functions in the function as datediff etc.. . plz rely me y its not working? or how can i achieved this
if i used this in the function, not in the SP.
March 5, 2007 at 4:14 am
Someone circumvented this in a recent post. The trick was to create a view dbo.VW_NOW (name of choice)with as definition: select getdate() as today
From the function use select today from dbo.VW_NOW instead of getdate().
March 5, 2007 at 4:51 am
You can't use non-deterministic functions within a user-defined function. That include rand(), newid() and getdate() amoung others
What you can do is write a more generic function that takes 2 params, and pass getdate as the second.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2007 at 7:19 am
Another solution is to not use the function at all. I know they are usefull for code reuse but in this case, the code is quite easy to remember. Also you get a small performance hit by using a function that you would not get by using straight sql in the query.
March 5, 2007 at 8:38 pm
As described in the 3 posting, non-deterministic functions are not allowed in UDFs under SQL Server 2000. Microsoft have obviously had a change of heart, because under SQL Server 2005, some non-deterministic functions (including GetDate()) are now allowed.
I would use the method described in posting (2) to create you own function and name it similar to GetDate (ie. fn_getdate()).
That way, when you upgrade to SQL Server 2005, you can replace all occurrences of your function with GetDate and all will be well.
March 5, 2007 at 11:05 pm
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=348736#bm348757
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply