getdate() inside a function

Why getdate() can be used in proc but not in function in this form:

SET @dat = GETDATE() - @time

where @dat datetime is local variable and @time int is an input.

Thanks!

GETDATE() is a built in function which is non deterministic, that is, it does not return the same value every time. Books Online states that non deterministic functions of this type are not allowed in the body of a UDF.

One solution is for your UDF to accept a date as a parameter, and call GETDATE() outside the function.

For more info see BOL->User defined functions->determinism

Actually there is a way to call GetDate() inside a function. What you should do is create a view that selects getdate

CREATE VIEW V_GetDate
AS
SELECT
Getdate() DateNow

Now from the function you can call it

SELECT DateNow FROM V_GetDate

Bambola.