Professional Web Applications Themes

Using GETDATE() within a user defined function - Microsoft SQL / MS SQL Server

Hi.... I am creating the following function: CREATE FUNCTION date_return (dateval datetime) RETURNS datetime AS BEGIN declare newdate as datetime if (dateval is null) set newdate=getdate() else set newdate=dateval return newdate END If the value passed is null, it will return the current date. The problem is that I am getting the following error: Error 443: Invalid use of 'getdate' within a function. Apparently I can not use getdate() to store the current date. What other alternatives do I have? I'll appreciate your help. Efrain...

  1. #1

    Default Using GETDATE() within a user defined function

    Hi....

    I am creating the following function:

    CREATE FUNCTION date_return (dateval datetime)
    RETURNS datetime AS
    BEGIN

    declare newdate as datetime

    if (dateval is null)
    set newdate=getdate()
    else
    set newdate=dateval

    return newdate

    END

    If the value passed is null, it will return the current date. The problem is
    that I am getting the following error:

    Error 443: Invalid use of 'getdate' within a function.


    Apparently I can not use getdate() to store the current date. What other
    alternatives do I have?

    I'll appreciate your help.

    Efrain


    Efrain Guest

  2. #2

    Default Re: Using GETDATE() within a user defined function

    http://www.aspfaq.com/2439




    "Efrain Flores" <com> wrote in message
    news:phx.gbl... 
    is 


    Aaron Guest

  3. #3

    Default Re: Using GETDATE() within a user defined function

    GETDATE() is a non-determinisitic function and cannot be used inside a
    function. One option is to create a VIEW that has just the date in it and
    then use that view inside the function.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Efrain Flores" <com> wrote in message
    news:phx.gbl... 
    is 


    SriSamp Guest

  4. #4

    Default Re: Using GETDATE() within a user defined function

    Thank You

    It worked perfectly.....


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    problem 
    >
    >[/ref]


    Efrain Guest

  5. #5

    Default Re: Using GETDATE() within a user defined function

    Thanks for your help...


    "SriSamp" <co.in> wrote in message
    news:phx.gbl... [/ref]
    problem 
    >
    >[/ref]


    Efrain Guest

Similar Threads

  1. Newbie - Calling a user-defined function
    By John in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 20th, 01:54 PM
  2. User-defined function slows down the view
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 18th, 09:07 PM
  3. Replies: 7
    Last Post: July 23rd, 10:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139