dateadd

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JEM - 28 Feb 2006 19:48 GMT

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?

Reply to this Message

Sylvain Lafontaine - 28 Feb 2006 20:32 GMT

It's year, not y

You have one ) to many at the end.

How and where do you use this Where statement?

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

>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
[quoted text clipped - 7 lines]
>
> Any ideas what i'm doing wrong here?

Reply to this Message

JEM - 28 Feb 2006 20:46 GMT

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.

Reply to this Message

Sylvain Lafontaine - 28 Feb 2006 20:51 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> 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
[quoted text clipped - 6 lines]
>
> Thanks for the help.

Reply to this Message

JEM - 28 Feb 2006 21:03 GMT

I didn't know that. Thanks.

Reply to this Message

Robert Morley - 28 Feb 2006 21:10 GMT

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

Reply to this Message