getdate() not working

Question

getdate() not working

isa-369599

SSCommitted

Points: 1602
More actions
March 5, 2007 at 4:06 am

#172286

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

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().

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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.

John Chapman SSC-Addicted Points: 457 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=348736#bm348757

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)