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

  • David le Quesne

    SSCertifiable

    Points: 5169

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

  • AJ Ahrens

    SSC-Insane

    Points: 20676

    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: 5169

    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: 716284

    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: 3073

    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: 11132

    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: 5169

    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

    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: 41820

    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