Passing GetDate() as a parameter to a UDF datetime parameter

Question

Post reply

Passing GetDate() as a parameter to a UDF datetime parameter

David le Quesne

SSCertifiable

Points: 5229
More actions
December 21, 2005 at 5:13 am

#90182

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...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

David le Quesne SSCertifiable Points: 5229 More actions · Answer 2

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...

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

Perhaps the UDF call takes precendence over the funciton call, so it's not a datetime when it's passed ?

Not sure

Bert De Haes Hall of Fame Points: 3081 More actions · Answer 4

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

vadba SSChampion Points: 11133 More actions · Answer 5

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.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 6

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...

Etore Newbie Points: 1 More actions · Answer 7

Perhaps helps anyone...

declare @data as char(10)

set @data= (select convert(varchar(10),getdate(),112) )

print @data

declare @data1 as datetime

set @data1 =convert(varchar(10),@data,112)

--print @data

print @data1

select * from UDF(@data1)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

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.