Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating Age Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 16, 2009 2:23 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, December 2, 2016 3:56 PM Points: 3,852, Visits: 6,566
 And Lynn's method declares the leap-year person to be 18 on the 28th. Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. :) SQL DBA,SQL Server MVP('07, '08, '09)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #676913
 Posted Monday, March 16, 2009 3:01 PM
 SSC-Insane Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 ScottPletcher (3/16/2009) And Lynn's method declares the leap-year person to be 18 on the 28th. Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. :)I also put a disclaimer in my article regarding leaplings. For purposes of my article I made a "business" decision to keep their birthday in February. Based on legal requirements, this could change, and we would then have to account for that in our calculations.
Post #676960
 Posted Thursday, April 2, 2009 1:02 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 9, 2009 1:30 AM Points: 2, Visits: 19
 For calculating age in the form of x Years y Months z Days check the post of minehttp://aspxdev.blogspot.com/2008/09/get-date-diference-in-form-of-x-years-y.html http://aspxdev.blogspot.com
Post #688566
 Posted Thursday, August 27, 2009 8:07 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, January 13, 2010 12:55 PM Points: 96, Visits: 35
 This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same: declare @BirthDate DATETIME set @BirthDate = '08/27/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) print 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/25/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/24/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) The following translates the dates into YYYYMMDD and gets the correct age, changing at at the month and year of the birtdate. DECLARE @CURRENT_YEAR INTEGER , @CURRENT_MONTHS INTEGER , @CURRENT_DAYS INTEGER , @CURRENT_YYYYMMDD INTEGER , @AGE_INTEGER INTEGER SET @CURRENT_YEAR = DATEPART(YEAR, GETDATE()) SET @CURRENT_MONTHS = DATEPART(MONTH, GETDATE()) SET @CURRENT_DAYS = DATEPART(DAY, GETDATE()) SET @CURRENT_YYYYMMDD = (@CURRENT_YEAR * 10000) + (@CURRENT_MONTHS * 100) + @CURRENT_DAYS SELECT 'CURRENT_DATE = ' , @CURRENT_YYYYMMDD declare @BirthDate DATETIME , @YEARS INTEGER , @MONTHS INTEGER , @DAYS INTEGER , @BIRTH_YYYYMMDD INTEGER set @BirthDate = '08/27/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER PRINT 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER set @BirthDate = '08/25/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER set @BirthDate = '08/24/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER
Post #778324
 Posted Thursday, August 27, 2009 8:57 AM
 SSC-Insane Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 David. (8/27/2009)This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same: declare @BirthDate DATETIME set @BirthDate = '08/27/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) print 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/25/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/24/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) Your right, DATEDIFF(yy, @birthdate, getdate()) will return the same value regardless if the @birthdate is before on or after GETDATE(). That is the nature of the DATEDIFF function. TO actually calculate age requires additional calculations to make the final determination.Consider this, DATEDIFF(yy,'2008-12-31','2009-01-01') returns 1 as there is a difference in year periods is 1 even though there is actually only a difference of 1 day.
Post #778384
 Posted Friday, October 23, 2009 11:01 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, June 5, 2015 5:33 PM Points: 46, Visits: 107
 Here is another way I calculate age. SELECT @AGE = ' '+CAST(((datediff(ss, @DOB,GETDATE())) /31536000) AS NVARCHAR(MAX)) + ' Years, ' + CAST((((datediff(ss, @DOB,GETDATE())) %31536000) /2628000) AS NVARCHAR(MAX)) + ' Months, ' + CAST((((datediff(ss, @DOB,GETDATE())) %2628000) /86400) AS NVARCHAR(MAX)) + ' Days, ' + CAST((((datediff(ss, @DOB,GETDATE())) %86400) /3600) AS NVARCHAR(MAX)) + ' Hours, ' + CAST((((datediff(ss, @DOB,GETDATE())) %3600) /60) AS NVARCHAR(MAX)) + ' Minutes, ' + CAST(((datediff(ss, @DOB,GETDATE())) %60) AS NVARCHAR(MAX)) + ' Seconds '
Post #808061
 Posted Wednesday, October 12, 2011 10:35 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 4, 2012 10:37 AM Points: 2, Visits: 62
 /*Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)average days in a year: 365.2425average days ion a month: 30.436875USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS*/CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDate datetime = NULL, @EndDate datetime = NULL)RETURNS varchar(7) WITH EXECUTE AS CALLERASBEGIN RETURN ( RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+ RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) + RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2) ) END;
Post #1189305
 Posted Wednesday, October 12, 2011 10:44 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, January 30, 2015 8:42 AM Points: 1,332, Visits: 19,324
 onecaring (10/12/2011)/*Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)average days in a year: 365.2425average days ion a month: 30.436875USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS*/CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDate datetime = NULL, @EndDate datetime = NULL)RETURNS varchar(7) WITH EXECUTE AS CALLERASBEGIN RETURN ( RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+ RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) + RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2) ) END;Interesting, if I set @StartDate = '1/1/2009' and @EndDate = '1/1/2010', I get 0001130, not one year. setting @EndDate = '1/2/2010' I get 0010001. ---------------------------------------------------------How best to post your questionHow to post performance problemsTally Table:What it is and how it replaces a loop"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."
Post #1189314
 Posted Thursday, October 13, 2011 7:46 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 4, 2012 10:37 AM Points: 2, Visits: 62
 Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.It is only my opinion... no one has to agree but positive criticism is most welcome
Post #1189834
 Posted Thursday, October 13, 2011 7:51 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, January 30, 2015 8:42 AM Points: 1,332, Visits: 19,324
 onecaring (10/13/2011)Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.It is only my opinion... no one has to agree but positive criticism is most welcome With the precision of years/months/days, I'd say they're exactly one year old on their birthday. ---------------------------------------------------------How best to post your questionHow to post performance problemsTally Table:What it is and how it replaces a loop"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."
Post #1189840

 Permissions