Search This Blog

How to 'GetDate()' inside a function

SQL server 2000 does not allow the users to call nondeterministic functions inside a udf. U can do this in 3 ways

1. Take date from sysprocess table
declare @date datetime
select @date =max(last_batch) from sysprocesses
2. Create a view and use it
CREATE VIEW Now
AS select getdate () RightNow
go
3. If u call this function from an sp, pass 'getdate()' as an argument
4. Create another function to return the value of GETDATE() using OPENQUERY method

Create Function dbo.Now()
RETURNS datetime AS
BEGIN
DECLARE @dt datetime
SELECT @dt = dt
FROM OPENQUERY ( ***urServerName***, 'SELECT dt = GETDATE()' )
Return @dt
END
GO

SELECT dbo.Now()

No comments: