dateadd

[JEM]

I searched for an answer and tried a couple examples, but i'm still
getting an error message...I am trying to query people whose birthday
makes them less than 19 years old from today:

....WHERE Birthday > DATEADD(y, - 19, CONVERT(char(10), GETDATE(),
101)))

but i keep getting an error message saying invalid use of getdate() in
a function.

Any ideas what i'm doing wrong here?

 

[Sylvain Lafontaine]

It's year, not y

You have one ) to many at the end.

How and where do you use this Where statement?

 

[JEM]

Thanks, i accidentally copied an extra ). Even with changing it, i
still get an error message. I am using it in a function, here is the
full sql:

SELECT [e-mail] AS Email, FName, LName, Birthday
FROM dbo.tblChild
WHERE (Auditions = 1) AND ([e-mail] IS NOT NULL) AND (Birthday >
DATEADD(year, - 19, CONVERT(char(10), GETDATE(), 101)))

Thanks for the help.

 

[Sylvain Lafontaine]

You cannot use GetDate() inside a User Defined Function (UDF) because UDF
must be deterministic; ie. returning the same result when called with the
same argument(s).

You will have to pass the value of GetDate() as an argument to the function.

 

[JEM]

I didn't know that. Thanks.

 

[Robert Morley]

Yes, I discovered that some time ago...pain in the posterior, isn't it?!?
It would be nice to be able to designate a function as being
pseudo-deterministic...in this case, able to assume one value for the entire
view/command/whatever being executed, but should not be assumed to be for a
different connection/view/command/whatever.

Don't suppose they added that in SQL 2005?



Rob