The Sql Server GETDATE() function not allowed in the body of user-defined functions, as it is non-deterministic. The GetNow view provides a means to generate a system date/time that can be used in Sql Server user-defined functions.
DROP VIEW dbo.GetNow
GO
CREATE VIEW dbo.GetNow AS
SELECT GetDate() Now
GO
GRANT SELECT ON dbo.GetNow TO PUBLIC
GO
Al Wood · June 29, 2006 at 3:45 am
So you implement it ike this:
– Returns getDate() by cheating
– Very naughty. Makes a non-deterministic function. Be aware of non-deterministic problems when you use this
CREATE FUNCTION dbo.getNowDatetime()
RETURNS datetime
BEGIN
/*
–If you need to re-create the view…
DROP VIEW dbo.GetNow
GO
CREATE VIEW dbo.GetNow AS
SELECT GetDate() Now
GO
GRANT SELECT ON dbo.GetNow TO PUBLIC
GO
*/
RETURN (select top 1 Now from GetNow)
END