GETDATE() in a Function

Hi again
I'm trying to write a user-defined function that accepts only one parameter, a date.
The function then calculates the amount of days elapsed between the specified date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to try an
calculate the difference, but GETDATE() keeps on causing an error
Can one use GETDATE() in a function
I tried to call a stored procedure from the same function, and an error states that
only extended stored procedures or functions can be called from within the function..
Any way to bypass this?Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:
>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter, a date.
>The function then calculates the amount of days elapsed between the specified date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error states that
>only extended stored procedures or functions can be called from within the function...
>Any way to bypass this?|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?