SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Age


Calculating Age

Author
Message
rmattaway
rmattaway
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 96
One method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.



Andrew in WV
Andrew in WV
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1376
Richard Briggs (3/13/2009)
Andrew (3/13/2009)
Richard Briggs (3/13/2009)
I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.


Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.

Andrew


I wouldn't store timestamp:

Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm w00t


That's kind of my point. If you're going to be that picky, you have to not only store the timestamp, you also have to correctly account for the timezone difference. Otherwise, it will be off somewhere.

Andrew

--Andrew
Rob Farley
Rob Farley
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 375
GSquared, you're welcome. Hope that method works for you (YYYYMMDD).

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
C# Gnu
C# Gnu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1245 Visits: 659
Andrew (3/13/2009)
Richard Briggs (3/13/2009)
Andrew (3/13/2009)
Richard Briggs (3/13/2009)
I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.


Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.

Andrew


I wouldn't store timestamp:

Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm w00t


That's kind of my point. If you're going to be that picky, you have to not only store the timestamp, you also have to correctly account for the timezone difference. Otherwise, it will be off somewhere.

Andrew

No you wouldn't need the timestamp, just shift the calculation using available timezone lookup data like this :

"67330048","67330559","US","UNITED STATES","TEXAS","DALLAS","32.7961","-96.8024","75201","-06:00"
"67330560","67330815","US","UNITED STATES","MISSOURI","COLUMBIA","38.9424","-92.3145","65201","-06:00"
"67330816","67331071","US","UNITED STATES","TEXAS","DALLAS","32.7961","-96.8024","75201","-06:00"
"67331072","67331327","US","UNITED STATES","FLORIDA","BRADENTON","27.4649","-82.5465","34201","-05:00"
"67331328","67331583","US","UNITED STATES","ARKANSAS","TEXARKANA","33.4765","-93.9191","71854","-06:00"
"67331584","67331839","US","UNITED STATES","TEXAS","PLANO","33.0404","-96.7238","75023","-06:00"

As I say, only useful for some applications, but interesting all the same.

Brigzy

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

ayesamson
ayesamson
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 515
I like to use computative column with datediff to calculate the age.
C# Gnu
C# Gnu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1245 Visits: 659
samsonjr2 (3/14/2009)
I like to use computative column with datediff to calculate the age.

Yes, its a shame you can't make it 'Persited' though, this means SQL has to recalc it with every query that includes that column.

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

ayesamson
ayesamson
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 515
Yes you are correct it recalcs.
Tim Wilson-Brown
Tim Wilson-Brown
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 302
rmattaway (3/13/2009)
One method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.


Most of the day-based solutions suffer from a fatal flaw:

My birthdays are never 365.25 (or .242199, or .2425) days apart.

They are either 365 or 366 days apart, with the number of days depending on:

1. Leap Years (this year or last year)
2. When I was born in the year: Before Feb 29th, on Feb 29th or after Feb 29th
3. The rules for Leapling birthdays in non-Leap Years

And for most real-world applications, being exact is important.

I want to be able to drink, drive, receive benefits, and get paid more on my birthday.
Approximations just aren't good enough Smile
Rob Farley
Rob Farley
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 375
Tim,

Exactly!

Suppose the case of my friend, who is born on May 11th, 1993. On their 18th birthday, the system using 365.25 will declare them still 17 - which can get them into all kinds of trouble depending on how they choose to celebrate legal drinking age (in Australia).

On the other hand, someone born on Feb 29th, 1992 will still be considered legally 17 on Feb 28th, 2010, and shouldn't be able to go drinking that day (or worse, be tried as an adult for some crime).

This is why using the YYYYMMDD method works best.

20110511 - 19930511 = 180000
20100228 - 19920229 = 179999

datediff(day,'19930511','20110511') / 365.25 = 17.998631

And Lynn's method declares the leap-year person to be 18 on the 28th.

Anyway - I think we all agree that 'age' can be awkward if the method for calculating isn't great.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
davidandrews13
davidandrews13
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1919 Visits: 4641
'works out the age of the apprentice.

Dim total As String
Dim strDOB As String = Format(CDate(.DateOfBirth), "MM dd")
Dim todaysdate As String = Format(CDate(Date.Today), "MM dd")


If strDOB > todaysdate Then
strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Hasn't had birthday yet this year

todaysdate = Format(CDate(Date.Today), "yyyy")
total = todaysdate - strDOB - 1
Else
strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Has had birthday this year
todaysdate = Format(CDate(Date.Today), "yyyy")
total = todaysdate - strDOB
End If

p1_txtAge.Value = total
p1_txtAge.Flatten = True

this is something i wrote in VB.NET
don't know if its any more accurate when working out the leap year issue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search