December 21, 2005 at 5:13 am
Hi,
I know you can't use GetDate() in a UDF, but does anyone know a way of passing the output from GetDate() into a DateTime UDF parameter.
I have a fairly simple function which returns a list of approvers. I can pass the date parameter as '21-Dec-2005', but if I try and use GetDate() in the function call I get an incorrect syntax error message.
CREATE FUNCTION ctfn_AdditionalApproval (@strUserID CHAR ( 8 ), @dtApprovalDate DATETIME )
RETURNS @Approvers TABLE (Approver CHAR(8) )
AS
BEGIN
INSERT INTO @Approvers (Approver)
SELECT APR.EmployeeID
FROM dbo.ApprovalRights APR WITH (NOLOCK)
WHERE APR.UserID = @strUserID
AND ISNULL(APR.EndDate, @dtApprovalDate) >= @dtApprovalDate
RETURN
END
David
If it ain't broke, don't fix it...
December 21, 2005 at 5:44 am
What we do is assign a variable the GETDATE() and then use that variable when we call the UDF. May help you
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 21, 2005 at 7:19 am
Thank you!
Assigning GetDate() to a DATETIME variable works, but GETDATE() returns a DATETIME datatype, so why doesn't passing it as a parameter directly work ?
David
If it ain't broke, don't fix it...
December 21, 2005 at 9:39 am
Perhaps the UDF call takes precendence over the funciton call, so it's not a datetime when it's passed ?
Not sure
December 22, 2005 at 1:26 am
To avoid the problem, I use my function dbo.fn_getdate(). This function can be used in other functions:
script ( create it in master, and call fn_getdate as master.dbo.fn_getdate() ) :
create table dbo.tbl_getdate
( id int,now as getdate())
go
insert dbo.tbl_getdate (id) values (1)
go
create function dbo.fn_getdate()
returns datetime
as
begin
declare @fn_getdate datetime
select top 1 @fn_getdate = now
from dbo.tbl_getdate
where id = 1
return @fn_getdate
end
go
grant exec on dbo.fn_getdate to public
go
December 22, 2005 at 7:28 am
Of the three types of UDF's, only scalar functions (functions that return a single value with the RETURN statement) allow the results of other functions to be passed as parameters. Table-value functions and inline functions, which return tables, do not. Actually, you can't use any functions in such calls. Try using CONVERT or RTRIM with the char parameter, for example - an error will be reported.
December 22, 2005 at 8:23 am
Thanks for all your help folks, I will try Bert's suggestion of a UDF version of GetDate()
Merry Christmas
David
If it ain't broke, don't fix it...
November 1, 2019 at 7:13 pm
Please note this is a 14 year old thread. The original function shown should now be written as a inline table valued function, not the way this is. Also, just tried it out and an inline table valued function can have the GETDATE() function passed in as a parameter.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy