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

Calculate Age Expand / Collapse
Author
Message
Posted Thursday, April 10, 2008 1:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:11 AM
Points: 478, Visits: 128
Comments posted to this topic are about the item Calculate Age
Post #483257
Posted Friday, May 23, 2008 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:16 AM
Points: 4, Visits: 48
why not just datediff() the number of days between the parameter and getdate() - then divide that number by 365.25?
Post #506096
Posted Friday, May 23, 2008 3:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 7, 2011 7:54 AM
Points: 1, Visits: 12
Dividing the datediff in days by 365.25 doesn't always work (consistently). Sometimes you have to delete the fraction part (floor) and sometimes you have to round up to the next integer (ceiling) to get the correct age. Here are two examples (assume the first date as DOB, and the second as the day the age is calculated on):
select datediff(day, '10/23/2006', '5/23/2008')/365.25, datediff(day, '5/23/1899', '5/23/2008')/365.25
returns
1.582477 108.999315

To get the right age in integer value, we have to floor the first value(1.582477), and ceiling the second value (108.999315).
Given that, I'd alter the original function by adding the second date as a parameter as well. Then you can pass getdate() or any other date as the second param.

-Prabhakar
Post #506156
Posted Friday, May 23, 2008 7:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:17 PM
Points: 2, Visits: 26
How 'bout this?

declare @dob as datetime
select @dob = '6/21/1968'
declare @now as datetime
declare @year_diff as tinyint
select @now = getdate()
select @year_diff = datediff(year, @dob, @now)
if DateAdd(year, @year_diff, @dob) > @now
select @year_diff -1
else
select @year_diff



basically, calculate the number of years that have gone by, then add those years back to the date of birth. if the resulting date is in the future, we subtract one from the years as it would indicate that we have not yet reached the birthday in the current year. if the resulting date is in the past, we have already reached the birthday in this year, so we can return the number of years unaltered.

thanks,
mark
Post #506188
Posted Wednesday, May 28, 2008 2:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:36 AM
Points: 60, Visits: 259
Thank you for idea

create function dbo.Calc_Age (@dob datetime) -- date of birth
returns int
as
begin
return ( select datediff (year, @dob,v_date) -
case
when (month(@dob) = month(v_date) and day(@dob) > day(v_date)) then 1
when month(@dob) > month(v_date) then 1
else 0
end
from dbo.v_getdate )
end
Post #508009
Posted Monday, June 2, 2008 3:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:11 AM
Points: 478, Visits: 128
Mark, your Idea is very nice :)
Post #509719
Posted Wednesday, August 19, 2009 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 28, 2009 10:52 AM
Points: 1, Visits: 1
Hello, Mark Ingalls,

This is Wasek, I am just a beginer in this business, but I am eager to get the solution of calculating a person's age and date difference from one date to another for my project. Could you pl make a sample project with those in VB 2008, Zip it and send it across to my e-mail address below I willbe pleased.

wasekm2001@yahoo.com

One more thing, if you know. Which I could not do. That, how can I change the page setup of a print form component. Meaning to say that its paper size and the margins are auto set. How can I set it my self of my desire. If you know give me easy solution. Again I need a sample project of yours in VB 2008, considering my capability as a learner. Thanks a lot.
Post #773801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse