Many times we need to write function. MS SQL Server provides the functionality to write user defined functions. We will see how user can define own function to compute the age of a individual, by passing Birth Date and Current Date:-

Here I have created a user defined function as follows…

CREATE FUNCTION [dbo].[fn_GetAge]
(
@DOB DATETIME ,
@AsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Age INT
IF @DOB >= @AsOfDate
RETURN 0

SET @Age = DATEDIFF(yy, @DOB, @AsOfDate)
IF MONTH(@DOB) > MONTH(@AsOfDate)
OR ( MONTH(@DOB) = MONTH(@AsOfDate)
AND DAY(@DOB) > DAY(@AsOfDate)
)
SET @Age = @Age – 1
RETURN @Age
END

 

How to Execute this Function:-

select [dbo].[fn_GetAge] (’02/04/1983′,’03/09/2011′)