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 specifie
d date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to t
ry 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 stat
es that
only extended stored procedures or functions can be called from within the f
unction...
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 specifi
ed 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 sta
tes 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?