Results 1 to 3 of 3

Thread: getdate() error in function

  1. #1
    Join Date
    Nov 2002
    Posts
    261

    getdate() error in function

    I have the below function which errors out telling me "Invalid use of 'getdate' within a function." I can run it as sql but not as a function, is there an issue with using getdate() in a function?

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO




    ALTER function fnGetQuantity( @orderid int )
    returns int
    as
    /************************************************** ****************************
    ** File: fnGetQuantity.sql
    ** Name: fnGetQuantity
    ** Desc: Used to calculate the total order quantity for an order
    ** Quantity is sigfrequency.timesperday * sigdosageamt.descr * orders.duration
    **
    ** Return values: Quantity
    **
    ** Called by:
    **
    ** Parameters: order Id
    ** Input Output
    ** ---------- -----------
    ** @patientid
    ** Auth: DHoefgen
    ** Date: 04/30/05
    ************************************************** *****************************
    ** Change History
    ************************************************** *****************************
    ** Date: Author: Description:
    ** -------- -------- ------------------------------------------
    ** 05/02/05 KKowert Changed sql for effdt and added duration and
    ** times per day logic for zeros.
    ************************************************** *****************************/
    begin
    declare @QuantityTotal int


    SELECT @QuantityTotal = (o.Duration * f.TimesPerDay * d.Descr)
    FROM Orders o INNER JOIN
    SIGFrequency f ON o.FreqID = f.FreqID INNER JOIN
    SIGDosageAmt d ON o.DosageAmtID = d.DosageAmtID
    WHERE (o.OrderID = @orderid) AND (f.Effdt =
    (SELECT MAX(f2.Effdt)
    FROM SIGFrequency f2
    WHERE f2.FreqID = f.FreqID AND f2.Effdt <= getdate())) AND (d.Effdt =
    (SELECT MAX(d2.Effdt)
    FROM SIGDosageAmt d2
    WHERE d2.DosageAmtID = d.DosageAmtID AND d2.Effdt <= getdate()))


    return @QuantityTotal

    end




    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You can't have non-determinstic system function in a function.

    Search for "Create function" in books online to see list of other functions not allowed in a function definition.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    pass the getdate as parameter

    create function dbo.previousday (@mydate datetime)
    returns datetime
    as
    begin
    return @mydate-1
    end
    go
    select dbo.previousday (getdate())

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •