SQL Server Error Messages - Msg 443 - GETDATE

Error Message

Server: Msg 443, Level 16, State 1, Line 1
Invalid use of 'getdate' within a function.

Causes:

This error is encountered when you use the GETDATE() function inside a user-defined function. Built-in nondeterministic functions, such as the GETDATE() function, are not allowed in the body of user-defined functions.

To illustrate, here’s a user-defined function that computes for the age given the date of birth:

CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth    DATETIME )
RETURNS INT
AS
BEGIN
    DECLARE @vAge         INT

    IF @pDateOfBirth >= GETDATE()
        RETURN 0

    SET @vAge = DATEDIFF(YY, @pDateOfBirth, GETDATE())

    IF MONTH(@pDateOfBirth) > MONTH(GETDATE()) OR
      (MONTH(@pDateOfBirth) = MONTH(GETDATE()) AND
       DAY(@pDateOfBirth)   > DAY(GETDATE()))
        SET @vAge = @vAge - 1

    RETURN @vAge
END
GO

Since the user-defined function used the GETDATE() function in its body, the function will not be created and the following error will be encountered:

Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 8
Invalid use of 'getdate' within a function.
Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 11
Invalid use of 'getdate' within a function.
Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 13
Invalid use of 'getdate' within a function.

Solution / Work Around:

To avoid this error, do not use the GETDATE() function inside the body of your user-defined function. If you really need to use the GETDATE() function, pass it as a parameter in your user-defined function. Here’s an updated version of the user-defined function above that computes for the age given the birthdate, with the reference date (GETDATE()) passed as a parameter:

CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth    DATETIME, 
                                     @pAsOfDate       DATETIME )
RETURNS INT
AS
BEGIN
    DECLARE @vAge         INT

    IF @pDateOfBirth >= @pAsOfDate
        RETURN 0

    SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

    IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
      (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
       DAY(@pDateOfBirth)   > DAY(@pAsOfDate))
        SET @vAge = @vAge - 1

    RETURN @vAge
END
GO