How to calculate Age/Number of Years between two dates

Question

Possible Duplicate:
How to calculate age in T-SQL with years, months, and days

On a project I was working on, I was required to calculate a persons age when they join the system; after looking on the internet I found various ways this could be done, but most of them had slight issues when it involved a Leap-Year.

The solution below is how I calculate number of years past / age. Hope this helps others

Can we assume that we have the birthdate? What is the problem with leap years? — Gordon Linoff, Dec 28 '12 at 15:47

score 18 · Accepted Answer · edited Nov 18 '15 at 10:25

You need to add the following method to your database:

CREATE FUNCTION [dbo].[fnCalAge] (@DiffFrom DATE, @DiffTo DATE) RETURNS INT
AS
BEGIN
    DECLARE @NumOfYears INT
    SET @NumOfYears = (SELECT 
                         DATEDIFF(YEAR, @DiffFrom, @DiffTo) + 
                         CASE 
                           WHEN MONTH(@DiffTo) < MONTH(@DiffFrom) THEN -1 
                           WHEN MONTH(@DiffTo) > MONTH(@DiffFrom) THEN 0 
                           ELSE 
                             CASE WHEN DAY(@DiffTo) < DAY(@DiffFrom) THEN -1 ELSE 0 END 
                         END)
    IF @NumOfYears < 0
    BEGIN
        SET @NumOfYears = 0;
    END

    RETURN @NumOfYears;
END

You then call it in your SQL Query, similar to the following:

SET DATEFORMAT dmy 

SELECT dbo.fnCalAge(CAST('20/06/1987' AS DATE), CAST('20/06/2013' AS DATE))

Charles Bretana · Answer 2 · 2014-10-07T15:13:54.840

8

assuming @bDate is datetime of birthdate and @today is todays date, then...

 Declare @bDay Date = '31 dec 2000'
 Declare @today Date = cast(getdate() as date)
 Select datediff(Year, @bDay, @today) - 
        case When datepart(dayofYear, @today) <
                  datepart(dayofYear, @bDay) Then 1 Else 0 End

edited Oct 07 '14 at 15:13

answered Dec 28 '12 at 15:51

Charles Bretana

143,358
22
150
216

This doesn't always give correct results because `datepart(dayOfYear, @date)` is skewed when leap years are taken into account i.e. the extra day in the year means your birthday will be calculated a day late when after 29th Feb. – MarkD Nov 19 '19 at 17:25

Art · Answer 3 · 2012-12-28T21:16:38.097

1

Replace hiredate with DOB for age. Replace sysdate with your date such as to_date('28-DEC-2012') :

SELECT empno, ename, hiredate, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12)  years_of_service 
 FROM scott.emp
/

edited Dec 28 '12 at 21:16

answered Dec 28 '12 at 21:02

Art

5,616
1
20
22

How to calculate Age/Number of Years between two dates

3 Answers3

Linked