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.
In previous articles i explained Query to get upcoming birthdays within week and Without primary key column update first or last n records in table and Update existing column values with linear serial numbers without loop and Get first,last date and total number of days in month and Query to split string from comma and get left and right part
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 )
BEGIN
PRINT 'Date of birth can not be greater than current date';
END
ELSE
BEGIN
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)]
END
Result:
Year(s)
|
Month(s)
|
Day(s)
|
27
|
9
|
12
|
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.
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..