Sql Server: Query to Get Age in Years, Months and Days from Date of Birth

Introduction: In this article I am going to explain how to calculate age in year, month and day from the Date of birth(DOB) field.

Description: While working with sql server database I got the requirement to calculate employee’s exact age in years, months and days from the known field Date of birth. Here I have shared the query that  used for this purpose.

Implementation: Let’s write sample query to get the desired result.

DECLARE @DOB DATE = '1988-09-24', @CurrentDate DATETIME = GETDATE(), @Years INT, @Months INT, @Days INT, @tmpFromDate DATE

IF(@DOB>@CurrentDate )
PRINT 'Date of birth can not be greater than current date';
SET @Years = DATEDIFF(YEAR, @DOB, @CurrentDate) - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @DOB, @CurrentDate), @DOB) > @CurrentDate THEN 1 ELSE 0 END)

SET @tmpFromDate = DATEADD(YEAR, @Years , @DOB)

SET @Months = DATEDIFF(MONTH, @tmpFromDate, @CurrentDate) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @CurrentDate),
@tmpFromDate) > @CurrentDate THEN 1 ELSE 0 END)

SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)

SET @Days = DATEDIFF(DAY, @tmpFromDate, @CurrentDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @CurrentDate),
@tmpFromDate) > @CurrentDate THEN 1 ELSE 0 END)

SELECT @Years AS [Year(s)], @Months AS [Month(s)], @Days [Day(s)]


Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..