<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Lynn Pettis
»
Calculating Age
90 posts, Page 8 of 9
««
«
5
6
7
8
9
»»
Calculating Age
Rate Topic
Display Mode
Topic Options
Author
Message
rmattaway
rmattaway
Posted Friday, March 13, 2009 4:07 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 3:18 PM
Points: 37,
Visits: 82
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
Andrew in WV
Andrew in WV
Posted Friday, March 13, 2009 10:40 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 114,
Visits: 1,182
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
Rob Farley
Rob Farley
Posted Friday, March 13, 2009 11:39 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 13, 2013 10:51 PM
Points: 163,
Visits: 301
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
C# Gnu
C# Gnu
Posted Saturday, March 14, 2009 5:38 AM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, June 15, 2013 5:17 PM
Points: 225,
Visits: 636
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
justsamson
justsamson
Posted Saturday, March 14, 2009 5:51 PM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 6:13 PM
Points: 25,
Visits: 340
I like to use computative column with datediff to calculate the age.
Post #675997
C# Gnu
C# Gnu
Posted Sunday, March 15, 2009 9:06 AM
SSC Veteran
Group: General Forum Members
Last Login: Saturday, June 15, 2013 5:17 PM
Points: 225,
Visits: 636
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
justsamson
justsamson
Posted Sunday, March 15, 2009 9:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 6:13 PM
Points: 25,
Visits: 340
Yes you are correct it recalcs.
Post #676102
Tim Wilson-Brown
Tim Wilson-Brown
Posted Sunday, March 15, 2009 6:11 PM
SSC Veteran
Group: General Forum Members
Last Login: Sunday, August 19, 2012 6:40 PM
Points: 202,
Visits: 301
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
Rob Farley
Rob Farley
Posted Sunday, March 15, 2009 7:36 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 13, 2013 10:51 PM
Points: 163,
Visits: 301
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
davidandrews13
davidandrews13
Posted Monday, March 16, 2009 10:49 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 463,
Visits: 2,714
'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 »
90 posts, Page 8 of 9
««
«
5
6
7
8
9
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
