# Calculating Age

• Calculating Age

if we can take date by '01/01/2008'

it's ok

first one is working

• this statement has some error-

Whe you consider the date with @dob = 1970-02-13 and

@day = 2008-07-09 . when we use this statement for calculating it will give 37 but the actual age is 38.

try this sql and comment on this.

declare @dob datetime,

@age int,

@day datetime

set @day = '2008-07-09'

set @dob = '1970-02-13'

select case when month(@day) > month(@dob) then year(@day) - year(@dob)

when month(@day) < month(@dob) then year(@day) - year(@dob) - 1

when day(@day) < day(@dob) then year(@day) - year(@dob) - 1

else year(@day) - year(@dob) end

• My Way to calculate...

DECLARE @dd AS DATETIME

SELECT @dd

Select DATEDIFF(yy, @dd, GETDATE()) - CASE

WHEN DATEPART(m, @dd) > DATEPART(m, GETDATE())

THEN 1

When DATEPART(m, @dd) = DATEPART(m, GETDATE()) AND DATEPART(d, @dd) > DATEPART(d, GETDATE())

THEN 1

When DATEPART(m, @dd) < DATEPART(m, GETDATE())

THEN 0

When DATEPART(m, @dd) = DATEPART(m, GETDATE()) AND DATEPART(d, @dd) <= DATEPART(d, GETDATE())

THEN 0

ELSE 0 END

ATif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sometimes, winning is not an issue but trying.
You can check my BLOG
[font="Arial Black"]here[/font][/url][/right]

• To add a comment that is to Lynn's point of "Use the solution that fits your situation"; I happen to be looking for the age in months in order to calculate a capitation payment (a flat rate payment per person per month), and in my situation if a member turns a year older mid-month, they are considered to be a year older from the 1st of that month forward.

In this case, my life is simple, as I can just count datediff(mm,@dob,@date) and be correct in my calculations.

Great discussion all, I found this useful the first time I read it and again months later, as I found the need.

Jon

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• Can I suggest an alternative?

Convert the strings into a number in the format YYYYMMDD (as you would for a data warehouse), and then just subtract one from the other. Strip off the last four digits, and you have the age.

Makes it:

(cast(convert(char(8),@SomeDate,112) as int) - cast(convert(char(8),@DOB,112) as int)) / 10000

Doesn't give you the months and days, etc... but it's really nice for 'number of years' - which is what we typically refer to as 'age'.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

• Doen't make sense I'm afraid. Maybe you must show us how your date variables have been declared. Here is two much simpler examples which might have been used in this thread before but I give it to you again.

Declare the variables:

`declare @somedate datetime = getdate()`

`declare @dob datetime`

`set @dob = convert(datetime,'01/01/1959',103)`

First example:

`select YEAR(@somedate)-YEAR(@dob) age`

Second example:

`select DATEDIFF(yyyy,@dob,@somedate) age`

You see here the KISS principal comes in: Keep it Simple, Stupid. My customers always tells me this when they think I'm making the systems too difficult. It's not about how fancy the code is you write but what is the fastest to write and will be more productive.:P:P:P:P:P:P:P

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

• Mine is just looking at the principle of:

Suppose your birthday is 19490713. Suppose today is 20090313.

Subtract one from the other, and you get:

20090313-19490713 = 599600

Divide by 1000 to see that the person is 59.

The day before their birthday, you get:

20090712-19490713 = 599999

And on their birthday, the difference becomes 600000, and they're 60 years old.

So the formula for it is just "Convert to YYYYMMDD, and subtract one number from the other. Then divide by 10000 ignoring the remainder."

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

• It was very simple code and interesting but someone wants to know the day of the birth date!

I added some piece script in your core script Lynn like here

declare @dob datetime,

@age int,

@day datetime

set @day = '2009-03-13'

set @dob = '1982-11-11'

set @age = datediff(yy,@dob,@day) -

case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end

select @age AS years, datename(w,@dob) AS birthday_date_name

And the results are:

years birthday_date_name

----------- ------------------------------

3 Friday

Lynn, you decide if you want to add the information of day name of DOB in your core script!

Dugi :hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• ok What about the following?

fair enough

Cheers..

• Sergio Lugo (6/30/2008)

Hi everyone !

I use this formula:

Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))

You can also put it in a calculated column.

It works (I think) because one year actually has 365.25 days... Like we only count 365 days in a regular year, we need compensate with an extra day every four years (leap year).

Try it and let me know how it goes !

this is how i calculate age. its concise and works on the concept that you will always get an extra day if you go back or forward any concecutive 4 years.

so it doesn't matter where you start, if you go back 6 years you will have 1 extra day. go back 8 and you will get 2 extra days.

• Apart from the earlier comment regarding 365.25, it also does not take into account the fact the leap years do not apply when the year can be divided by both 4 AND 100 - which admittedly does not happen often, but is still another factor of imprecision.

I've used the method described by a previous commenter, in which dates are converted to YYYYMMDD, after which the birthdate is subtracted from the current date and the first four digits (or divide-by-1000) are used to determine the age, for quite some time now. Since I've heard no comments on it by other people here, are there caveats to keep in mind when using this method?

Cheers,

Sander

• Prashant (3/13/2009)

DECLARE @BirthDate datetime

DECLARE @CurrentDate datetime

DECLARE @Age int

SET @BirthDate = '2008-03-13 10:10:00' /* OR Whatever date you want */

SET @CurrentDate = GETDATE() /* OR Whatever date you want */

--This gives accuracy up to a Day

SET @Age = DATEDIFF(dd,@BirthDate,@CurrentDate)/365

SELECT @Age

--This gives accuracy up to an Hour

SET @Age = DATEDIFF(hh,@BirthDate,@CurrentDate)/8760

SELECT @Age

--This gives accuracy up to a Minute

SET @Age = DATEDIFF(mi,@BirthDate,@CurrentDate)/525600

SELECT @Age

--This gives accuracy up to a Second

SET @Age = DATEDIFF(ss,@BirthDate,@CurrentDate)/31536000

SELECT @Age

Cheers..

Interesting trivia, but ultimately not very useful.

There are (according to Google) 365.242199 days in a year, not 365 nor even 365.25 as many have suggested. (That's why every 100th year is NOT a leap year even though 100 is evenly divisible by 4, unless it is also every 400th year.) Based on that number, there would be approximately 8765.81277 hours in a year (525948.766 minutes, or 31556926 seconds, ...)

Still, others have already posted about issues with calculating age by dividing the number of days between to dates and dividing between whatever number you choose to use for the number of days in a year. For most dates it might work, but the boundaries will get you.

As for the hours, minutes, seconds, etc., those calculations are pointless. Aside from the fact that by the time you calculate your age to the nearest second your answer is already incorrect 😉 , your answer will be off unless you somehow factor in the time zones for the events, changes due to DST (not just within a year, but year to year when some government decides to change the rules as we recently did in the US), etc. One way would be to store all your dates in UTC, but then how many DOB fields have you seen that include a complete timestamp down to the seconds? I'm sure there might be some in the medical field, but beyond that I doubt you'll find many.

Andrew

--Andrew

• Andrew (3/13/2009)

how many DOB fields have you seen that include a complete timestamp down to the seconds? I'm sure there might be some in the medical field, but beyond that I doubt you'll find many.

Andrew

No doctor is logging the seconds. Guarantee. Unless you're paying them extra for it.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• Most of the solutions I see here have flaws, in that they do not handle one or more of these scenarios: (1) it is a leap year, (2) the DOB is greater than the date being passed in (3) today is the person's birthdate. The following code was written by my colleague Feng. It handles all of these situations and (so far) appears to be bullet-proof. Notice that the solution for (2) is recursive.

[font="Courier New"]ALTER FUNCTION [dbo].[GetAge]

(@thedate DATETIME

,@theDOB DATETIME)

RETURNS int

AS

BEGIN

if (@theDOB > @thedate) return - dbo.GetAge(@theDOB,@thedate)

declare @theage as int

set @theage = datediff(year,@theDOB,@thedate)

if (@theage = 0 ) return @theage

--Not a year

if (datediff(day,@thedate, @theDOB) >0 ) set @theage=@theage -1

RETURN @theage[/font]