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

  • 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) )



      INSERT INTO @Approvers (Approver)

      SELECT APR.EmployeeID

      FROM dbo.ApprovalRights APR WITH (NOLOCK)

      WHERE APR.UserID = @strUserID

      AND ISNULL(APR.EndDate, @dtApprovalDate) >= @dtApprovalDate




    If it ain't broke, don't fix it...

  • 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

  • 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 ?


    If it ain't broke, don't fix it...

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

    Not sure

  • 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())


    insert dbo.tbl_getdate (id) values (1)


    create function dbo.fn_getdate()

    returns datetime



     declare @fn_getdate datetime

     select top 1 @fn_getdate = now

     from dbo.tbl_getdate

     where id = 1

     return @fn_getdate



    grant exec on dbo.fn_getdate to public


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


  • Thanks for all your help folks, I will try Bert's suggestion of a UDF version of GetDate()

    Merry Christmas


    If it ain't broke, don't fix it...

  • 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)



  • 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 8 (of 8 total)

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