calculating age from sysdate and birthdate using SQL Server

Question

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

I'm simply trying to calc age (in years) using a birthdate variable

1932-04-29 00:00:00.000

and SYSDATETIME( ) in SQL Server, where by

SELECT year(Sysdatetime() - Birthdate) as Age

produces (surprisingly) : 1980

What did I miss? I expected to get 80!

podiluska · Accepted Answer · 2013-09-05T22:03:29.850

8

Calculating age is not as simple as it might first appear.

If you use Datediff you get a difference in absolute years, which is not the age.

eg

select DATEDIFF(yy, '1980-12-31', getdate())

will return 32, whereas the age of the person in question is 31.

This might be accurate enough for your purposes. More accurate, but still wrong, you can use

select convert(int,DATEDIFF(d, '1933-10-31', getdate())/365.25)

which is right most of the time.

Or you can write a more complex function....

edited Sep 05 '13 at 22:03

answered Oct 31 '12 at 15:33

podiluska

50,950
7
98
104

This seems to round up. Is there a way to not have it round up? – Frantumn Jul 31 '14 at 15:04
What's with dividing it by 365.25? Does this consider the leap years? – Ankit Suhail Sep 27 '17 at 14:32

score 1 · Answer 2 · answered Oct 31 '12 at 15:43

CREATE Function [dbo].[F_GetAge]( @RefDate Datetime,@Birthdate Datetime) Returns Int as
/*
200040916 Thomas Wassermann
*/
Begin
    Declare @Alter Int
    if @RefDate>@Birthdate
    Select @Alter=(DatePart(yy,CAst(@Refdate-@Birthdate -1 as Float))-1900)
    else select @Alter=0
    Return(@Alter) 
end

score 1 · Answer 3 · answered Oct 31 '12 at 15:59

Quick and dirty. Can have some errors bepending on leap years, etc.

-- quick and dirty age in years

declare @birthday datetime

set @birthday = '19320429'

-- this is pretty close, but could have problems with leap years, etc.
select floor(datediff(day, @birthday, CURRENT_TIMESTAMP) / 365.25)

score 0 · Answer 4 · edited May 23 '17 at 12:10

What happens here is the following:

both dates get converted to floats as number of days since 1900-01-01T00:00:00
the floats get subtracted from each other yielding the number of days between them
the result gets converted back to a date by adding the number of days to the base date

To correctly calculate the difference use DATEDIFF: http://msdn.microsoft.com/en-us/library/ms189794.aspx

But there is still a problem with that. See here for a complete solution: How to calculate age (in years) based on Date of Birth and getDate()

calculating age from sysdate and birthdate using SQL Server

4 Answers4

Linked

Related