SQL Server Performance

getdate() inside a function

Discussion in 'General Developer Questions' started by mtmingus, May 30, 2003.

  1. mtmingus New Member

    Why getdate() can be used in proc but not in function in this form:

    SET @dat = GETDATE() - @time

    where @dat datetime is local variable and @time int is an input.

    Thanks!
  2. Chappy New Member

    GETDATE() is a built in function which is non deterministic, that is, it does not return the same value every time. Books Online states that non deterministic functions of this type are not allowed in the body of a UDF.

    One solution is for your UDF to accept a date as a parameter, and call GETDATE() outside the function.

    For more info see BOL->User defined functions->determinism
  3. bambola New Member

    Actually there is a way to call GetDate() inside a function. What you should do is create a view that selects getdate

    CREATE VIEW V_GetDate
    AS
    SELECT
    Getdate() DateNow

    Now from the function you can call it

    SELECT DateNow FROM V_GetDate

    Bambola.



  4. satya Moderator

Share This Page