GetDate() as parameter to UDF

I understand that GetDate() cannot be used within the *body* of a
user-defined function because it is non-deterministic.
I now have a table-valued function with which I want to use DateAdd and
GetDate() as *parameters* -- thus:
Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
I get "Incorrect syntax near '('.
The same happens if I eliminate the DateAdd. If I use hard-coded dates,
e.g. '1/1/2006', the function works as expected.
What am I doing wrong?
Thanks.
Daniel WilsonDaniel Wilson a crit :
> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
Use a view instead...
here is an exemple :
CREATE VIEW dbo.V_DATE_TIME_DTM
AS
SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
GO
CREATE FUNCTION dbo.F_CURRENT_DATE ()
RETURNS DATETIME
AS
BEGIN
-- date/time with time to ZERO
DECLARE @.D DATETIME
SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
FROM dbo.V_DATE_DAT
RETURN @.D
END
GO
A +

> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Declare variables and set their values and pass them to the function:
declare @.dt datetime
set @.dt = getdate()
select * from dbo.myfn(@.dt)
"Daniel Wilson" wrote:

> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
>|||Thank you, both.
We're working with the view solution b/c we're using the function within a
view.
dwilson
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ee4GCboUGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Daniel Wilson a crit :
> Use a view instead...
> here is an exemple :
> CREATE VIEW dbo.V_DATE_TIME_DTM
> AS
> SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
> GO
>
> CREATE FUNCTION dbo.F_CURRENT_DATE ()
> RETURNS DATETIME
> AS
> BEGIN
> -- date/time with time to ZERO
> DECLARE @.D DATETIME
> SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
> FROM dbo.V_DATE_DAT
> RETURN @.D
> END
> GO
>
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************