Hello Friends, In this tutorial I will show you a simple User Defined Function to calculate Age from Date Of Birth in SQL Server.

User Defined Function to Calculate Age from Date Of Birth in SQL Server:

CREATE FUNCTION udfGetAge
(
@date datetime
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @tmpdate datetime, @years int, @months int, @days int


SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
RETURN 'Your Age is '+ CAST(@years AS VARCHAR(10))+' Year,'+ CAST(@months AS VARCHAR(10))+' Month,'+ CAST(@days AS VARCHAR(10))+' Days'
END

Test Function:

SELECT [dbo].[udfGetAge]('06/20/1993')

Output:

Calculate Age

View More:

Conclusion:

I hope you would love this post. Please don’t hesitate to comment for any technical help. Your feedback and suggestions are always appreciated.

Thank You.

Leave a Comment