Post reply

Getdate Function Error

  • May 28, 2003 at 5:26 am

    #80948

    I am getting the error message below when using getdate() function in a suer defined function:

    ==========================================

    An invalid use of the Getdate function within a function may cause an access violation and the following error message appears in Query Analyzer:

    Server: Msg 443, Level 16, State 1, Procedure fnTestDate, Line 4

    Invalid use of 'getdate' within a function.

    ODBC: Msg 0, Level 19, State 1

    SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005

    EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    Connection Broken

    ========================================

    Microsoft claimed to have fixed the problem in service pack2 for sql2000. I am getting the error in sql2000 with sp 3 applied.

    Has anyone come across the same problem?. Your help will be greatly appreciated.

  • May 28, 2003 at 5:45 am

    #458803

    Have you isntalled any hot fixes. If so it may have revisioned a file backwards. Check your @@Version as well. I am running 2000 with SP3 and get no access violations with it, just the message "Invalid use of 'getdate' within a function.".

  • May 28, 2003 at 6:01 am

    #458806

    Thanks. Does it mean I can not use getdate within a a user defined function.?

    How can I work around this?

  • May 28, 2003 at 7:05 am

    #458818

    From BOL

    quote:

    Function Determinism and Side Effects

    Functions are either deterministic or nondeterministic. They are deterministic when they always return the same result any time they are called with a specific set of input values. They are nondeterministic when they could return different result values each time they are called with the same specific set of input values.

    Nondeterministic functions can cause side effects. Side effects are changes to some global state of the database, such as an update to a database table, or to some external resource, such as a file or the network (for example, modify a file or send an e-mail message).

    Built-in nondeterministic functions are not allowed in the body of user-defined functions; they are as follows:

    @@CONNECTIONS

    @@TOTAL_ERRORS

    @@CPU_BUSY

    @@TOTAL_READ

    @@IDLE

    @@TOTAL_WRITE

    @@IO_BUSY

    GETDATE

    @@MAX_CONNECTIONS

    GETUTCDATE

    @@PACK_RECEIVED

    NEWID

    @@PACK_SENT

    RAND

    @@PACKET_ERRORS

    TEXTPTR

    @@TIMETICKS

    Although nondeterministic functions are not allowed in the body of user-defined functions, these user-defined functions still can cause side effects if they call extended stored procedures.

    Functions that call extended stored procedures are considered nondeterministic because extended stored procedures can cause side effects on the database. When user defined functions call extended stored procedures that can have side effects on the database, do not rely on a consistent result set or execution of the function.

    You cannot use GETDATE inside the UDF however you could call GETDATE as a submitted value to a UDF to work with it.

  • May 28, 2003 at 8:39 am

    #458848

    Thanks a lot for your help.

  • May 28, 2003 at 6:39 pm

    #458935

    A while ago played around by creating a view like "create view select getdate()" and then access it from the udf.

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

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