GETDATE() in function - BUG

  • 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

  • What is the format of varable @DOB?

  • 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.

  • 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.

  • 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