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 Monday, March 16, 2009 2:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #676913
Posted Monday, March 16, 2009 3:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 22,992, Visits: 31,471
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #676960
Posted Thursday, April 2, 2009 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 mine
http://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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 22,992, Visits: 31,471
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #778384
Posted Friday, October 23, 2009 11:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:04 PM
Points: 45, Visits: 97
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.2425
average days ion a month: 30.436875

USAGE: 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 CALLER

AS
BEGIN

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 2,656, Visits: 19,182
onecaring (10/12/2011)
/*
Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)

average days in a year: 365.2425
average days ion a month: 30.436875

USAGE: 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 CALLER

AS
BEGIN

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 question
How to post performance problems
Tally 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 2,656, Visits: 19,182
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 question
How to post performance problems
Tally 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
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse