Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Calculating Age Expand / Collapse
Author
Message
Posted Friday, March 13, 2009 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 10:08 AM
Points: 37, Visits: 90
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.


Post #675672
Posted Friday, March 13, 2009 10:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:16 AM
Points: 133, Visits: 1,269
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


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
Post #675777
Posted Friday, March 13, 2009 11:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
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
Post #675789
Posted Saturday, March 14, 2009 5:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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


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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #675848
Posted Saturday, March 14, 2009 5:51 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:36 PM
Points: 25, Visits: 375
I like to use computative column with datediff to calculate the age.
Post #675997
Posted Sunday, March 15, 2009 9:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #676096
Posted Sunday, March 15, 2009 9:15 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:36 PM
Points: 25, Visits: 375
Yes you are correct it recalcs.
Post #676102
Posted Sunday, March 15, 2009 6:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 5:40 PM
Points: 209, 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 :)
Post #676201
Posted Sunday, March 15, 2009 7:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
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
Post #676210
Posted Monday, March 16, 2009 10:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:56 AM
Points: 575, Visits: 3,390
'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
Post #676721
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse