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