# How to Calculate Age in Sql Server

Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

```DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'```

Incorrect Result:

```Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)```

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

```DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE())
-
(CASE
>  GETDATE() THEN 1
ELSE 0 END)
'Age in Years'```

Correct Result:

```Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)```

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.

## 21 thoughts on “How to Calculate Age in Sql Server”

1. Amirtharaj says:

Sql server 2005 code:

DECLARE @DOB datetime
set @DOB =’05/01/1982′
SELECT @DOB ‘Date of Birth’, GETDATE() ‘Current Date’,
DATEDIFF(YEAR,@DOB,GETDATE())

(CASE
> GETDATE() THEN 1
ELSE 0 END)
‘Age in Years’

2. Lodewijk says:

What a complicated code. Just do this:
age=
floor(
( cast(convert(varchar(8),getdate(),112) as int)-
cast(convert(varchar(8),@dob,112) as int) ) / 10000
)

1. upendra says:

how to calculate age on dob(from db)

1. –Create table variable to hold our test records
DECLARE @Workers TABLE (WorkerName VARCHAR(50), DOB DATE);

–Insert test records
INSERT INTO @Workers
SELECT ‘Ryan’, ‘1972-08-24’ UNION ALL
SELECT ‘James’, ‘1985-09-26’ UNION ALL
SELECT ‘Jasson’, ‘1983-08-25’ UNION ALL
SELECT ‘Tara’, ‘1991-09-24’ UNION ALL
SELECT ‘William’, ‘1992-08-19’ UNION ALL
SELECT ‘Judy’, ‘1989-09-15’;

–Variable to provide required number of days
DECLARE @InNextDays INT;
SET @InNextDays = 3;

— Query to find workers, whose birthday is in given number of days
SELECT *
FROM @Workers
WHERE DATEADD( Year, DATEPART( Year, GETDATE()) – DATEPART( Year, DOB), DOB)
BETWEEN CONVERT( DATE, GETDATE())
AND CONVERT( DATE, GETDATE() + @InNextDays);

2. TDGA says:

This was just what I needed!!!
Thanks a million 🙂

1. TDGA says:

The solution from Lodewijk 🙂

3. HSobo says:

Thank you, I agree that yours is the simplest code. I knew there had to be a way being MS ACCESS can do it with the following code:

3. ram says:

1. NAVEEN says:

SELECT
DOB,
DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
CASE
THEN DATEDIFF(YY,DOB,GETDATE()) -1
ELSE DATEDIFF(YY,DOB,GETDATE())
END as age
FROM WORKERS

1. NAVEEN DB says:

SELECT
DOB,
DATEDIFF(YY,DOB,GETDATE()) AS YEAR,
CASE
THEN DATEDIFF(YY,DOB,GETDATE()) -1
ELSE DATEDIFF(YY,DOB,GETDATE())
END as age
FROM WORKERS

4. m.sirajRaza says:

sir I want to make a function in which I pas a date and the function will calculate difference between passes date and getdate.
but not only year I want to year , month and days
like this
**years,**months,**days

1. Joemon says:

declare @date_of_birth as datetime=’2015-12-31 10:00:01′;
Select cast((DATEDIFF(m, @date_of_birth, GETDATE())/12) as varchar) + ‘ Y & ‘ +
cast((DATEDIFF(m, @date_of_birth, GETDATE())%12) as varchar) + ‘ M & ‘ +
–cast((DATEDIFF(m, @date_of_birth, GETDATE())%31) as varchar) + ‘ D ‘
cast((datepart(d,GETDATE())-datepart(d,@date_of_birth)) as varchar) + ‘D’
as Age

5. Howard Rothenburg says:

DECLARE @Test TABLE (
EmpName VARCHAR(40) ,
BirthDate DATE
);

INSERT INTO @Test ( EmpName , BirthDate
)
VALUES ( ’30 Yrs old yesterday’ , DATEADD(day , -1 , DATEADD(year , -30 , GETDATE()))
) , ( ’30 Yrs old today’ , DATEADD(year , -30 , GETDATE())
) , ( ’30 Yrs old tomorrow’ , DATEADD(day , 1 , DATEADD(year , -30 , GETDATE()))
);

SELECT EmpName , BirthDate ,
CASE
WHEN DATEADD(YY , DATEDIFF(yy , BirthDate , GETDATE()) , BirthDate) < GETDATE()
THEN DATEDIFF(yy , BirthDate , GETDATE())
ELSE DATEDIFF(yy , BirthDate , GETDATE()) – 1
END AS Age
FROM @Test;

6. Howard Rothenburg says:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetAge
(@DOB DATETIME,
@ToDate DATETIME
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Diff SMALLINT,
@F DATETIME,
@T DATETIME

SELECT @Diff = DATEDIFF(month, @DOB, @ToDate) / 12,
@F = DATEADD(year, 2000 – DATEPART(year, @DOB), @DOB),
@T = DATEADD(year, 2000 – DATEPART(year, @ToDate), @ToDate)

IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0
BEGIN
IF @DOB @T
SELECT @Diff = @Diff – 1

IF @DOB > @ToDate AND @F < @T
SELECT @Diff = @Diff + 1
END

RETURN @Diff
END

7. Howard Rothenburg says:

CREATE FUNCTION [dbo].[ufn_GetAge]
(@pDateOfBirth DATETIME,
@pAsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @vAge INT;
IF @pDateOfBirth >= @pAsOfDate
RETURN 0;
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate);
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate)
OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate)
AND DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge – 1;
RETURN @vAge;
END;
GO

8. Ash Silver says:

This solution works except for if somebody’s birthday is Feb 29th and its being measured on Feb 28th the following year. In some countries its the birthday but in most countries the legal birthday is March 1 the following (non-leap) years.

9. Create FUNCTION [dbo].[GetCurrentAge]
(
@DOB Date
)
RETURNS int

AS
BEGIN

Declare @CurrentAge int
Declare @Correction int
Select @Correction=0

Select @CurrentAge=DateDiff(YEAR,CONVERT(DATETIME, @DOB),GETDATE())
BEGIN
Select @Correction=-1
END
Select @CurrentAge=@CurrentAge+@Correction
Return @CurrentAge
END

10. Pranay Brahmbhatt says:

We can also use

select datediff(yy,birthdate,getdate()) as age from tablename

Where the birthdate is a column in table

11. Hi, How may I calculate th hours past from birth date of baby neonatal to deathdate?

here my query
SELECT d.YearMonth,
(DATEDIFF(hh,ud.Birthdate, ud.UnitDischargeDateTime)) % 24 AS Hours_Taken
,(DATEDIFF(dd,ud.Birthdate, ud.UnitDischargeDateTime)) AS Age_Days
,DATEDIFF(DAY, ud.Birthdate,ud.UnitDischargeDateTime) -(DATEDIFF(WEEK, ud.Birthdate,ud.UnitDischargeDateTime) * 2) as daylived
,DATEDIFF(hh,CONVERT(TIME, ud.Birthdate),CONVERT(TIME, ud.UnitDischargeDateTime))/24.0 as hourslived
,dateadd(hh, -24, DateDiff(hh, getdate(), ud.Birthdate)) as Last24H

FROM DB_MONITOR.dbo.report_SplitIntervalFirstLastDay(@BeginDate, @EndDate) as d,#temp_UnitAdmission as ud
JOIN #temp_NeonateUnit as neo ON neo.UnitId = ud.UnitId
AND ud.UnitDischargeCode = ‘D’

WHERE ud.UnitDischargeDateTime >= d.FirstDay AND ud.UnitDischargeDateTime <= d.LastDay
GROUP BY d.YearMonth, ud.Birthdate, ud.UnitDischargeDateTime;

12. Hi, How are you? can you help?
How to calculate the hours past from birth date of baby neonatal to death date?

This is my query!
SELECT d.YearMonth,
(DATEDIFF(hh,ud.Birthdate, ud.UnitDischargeDateTime)) % 24 AS Hours_Taken
,(DATEDIFF(dd,ud.Birthdate, ud.UnitDischargeDateTime)) AS Age_Days
,DATEDIFF(DAY, ud.Birthdate,ud.UnitDischargeDateTime) -(DATEDIFF(WEEK, ud.Birthdate,ud.UnitDischargeDateTime) * 2) as daylived
,DATEDIFF(hh,CONVERT(TIME, ud.Birthdate),CONVERT(TIME, ud.UnitDischargeDateTime))/24.0 as hourslived
,dateadd(hh, -24, DateDiff(hh, getdate(), ud.Birthdate)) as Last24H

FROM DB_MONITOR.dbo.report_SplitIntervalFirstLastDay(@BeginDate, @EndDate) as d,#temp_UnitAdmission as ud
JOIN #temp_NeonateUnit as neo ON neo.UnitId = ud.UnitId
AND ud.UnitDischargeCode = ‘D’

WHERE ud.UnitDischargeDateTime >= d.FirstDay AND ud.UnitDischargeDateTime <= d.LastDay
GROUP BY d.YearMonth, ud.Birthdate, ud.UnitDischargeDateTime;