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 = Dateadd(YEAR,-10, getdate())
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
-
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 AfricaI 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:
-
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)
ok What about the following?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
set @theDOB = dateadd(year,@theage,@theDOB)
--Not a year
if (datediff(day,@thedate, @theDOB) >0 ) set @theage=@theage -1
RETURN @theage[/font]
-
Hey Comrades
how about creating a Peristed Calulated column in your 'person' table.
The column would call one of the function solutions you have discussed.
This makes is easier / quicker to list (say) people older than 50.
I think if you had a databse of say 20 millions patients (health care type sysem) the performance boost of not having to calulate age in the query would be advantageous.
Brigzy
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015
Viewing 15 posts - 31 through 45 (of 93 total)
You must be logged in to reply to this topic. Login to reply