October 17, 2002 at 9:31 am
I am trying to create a simple function that has
datediff with one parameter as GETDATE() --set @Age=datediff(year, @DOB, GETDATE())--
and I get the following error.
Server: Msg 443, Level 16, State 1, Procedure fPhysCertExp, Line 10
Invalid use of 'getdate' within a function.
I checked the MSDN and it says it was a bug (BUG #: 353429 (SHILOH_BUGS) ), fixed supposedly in SP2. I have SP2 and the latest patches up to yesterdays (OCT 16), but the bug is still there. Tested on two servers.
anybody has an idea how to fix this?
Thanks,
Jakub
October 17, 2002 at 9:43 am
What is the format of varable @DOB?
October 17, 2002 at 10:08 am
Getdate() cannot be used in User Defined Functions (because it is not deterministic).
I guess in this instance, the current date would have to be passed in as a parameter.
October 17, 2002 at 11:36 am
I even hardcoded the @DOB in the statement to check it.
DaveT
you are correct. I found this in BOL when digging around.
'Built-in nondeterministic functions are not allowed in the body of user-defined functions.'
BUT
that is NOT what the MSDN says. They say it was a bug and was fixed.
Well.. it would not be the first time.
The passing of the argument to the function is a good solution.
Thanks.
J.
October 18, 2002 at 4:53 am
You apparently misread. The bug was that it can cause an access violation instead of just the error message you see.
Take a look at the KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q308759
It does not change the fact a function can use GETDATE() internally, just prevents it from blowing up the server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply