Age Function F_AGE_IN_YEARS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Age Function F_AGE_IN_YEARS

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-04 : 15:53:56

Function F_AGE_IN_YEARS in the script below calculates age in years from @START_DATE through @END_DATE and returns the age as an integer.

Age is the number of full years between @START_DATE and @END_DATE.

The script includes a query to demo the function, and sample results.

This function is a companion to the function on the following link that calculates Age in format YYYY MM DD to calculate age to the precision of days.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

Other information about working with SQL Server datetime can be found on the following link.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762



if objectproperty(object_id(N'[dbo].[F_AGE_IN_YEARS]'),'IsScalarFunction') = 1
	begin drop function [dbo].[F_AGE_IN_YEARS] end
go
create function dbo.F_AGE_IN_YEARS
(
	@START_DATE		datetime,
	@END_DATE		datetime
)
returns		int
as
/*
Function F_AGE_IN_YEARS computes Age in years.

	Input parameters @START_DATE and @END_DATE
	are required.  If either or both parameters
	are null, the function returns null.
	if @START_DATE midnight is greater than
	@END_DATE midnight, the function returns NULL.

	Age is defined as the number of anniversary dates
	reached or passed from @START_DATE through @END_DATE.

	Age is calculated based on midnight (00:00:00.000)
	of parameters @START_DATE and @END_DATE.
	Time of day is not used in the calculation.

	For example, someone born 2000-02-15
	would be 5 years old on 2006-02-14, 
	but 6 years old on 2000-02-15.

	Someone born on Feb 29 would be a year
	older on Feb 28 in non-leap years, but
	would be a year older on Feb 29 in leap years.

	Function is valid for entire range of datetime
	values from 1753-01-01 00:00:00.000 to 
	9999-12-31 23:59:59.997.

*/

begin

declare @AGE_IN_YEARS int

select	@START_DATE	= dateadd(dd,datediff(dd,0,@START_DATE),0),
	@END_DATE	= dateadd(dd,datediff(dd,0,@END_DATE),0)

if @START_DATE > @END_DATE
	begin
	return null
	end

select
	@AGE_IN_YEARS =
	datediff(yy,StartDateYearStart,EndDateYearStart) +
	-- Subtract 1 if anniversary date is after end date
	case
	when AnniversaryThisYear <= @END_DATE
	then 0
	else -1
	end
from
(
select	AnniversaryThisYear =
	dateadd(yy,datediff(yy,StartDateYearStart,EndDateYearStart),@START_DATE), 
	StartDateYearStart,
	EndDateYearStart
from
(
select	StartDateYearStart =
		dateadd(yy,datediff(yy,0,@START_DATE),0),
	EndDateYearStart  =
		dateadd(yy,datediff(yy,0,@END_DATE),0)
) aa
) a

return @AGE_IN_YEARS

end
go

print 'Demo Age function F_AGE_IN_YEARS'
print ''

select 
	[Age] = dbo.F_AGE_IN_YEARS( a.START_DATE, a.END_DATE ),
	START_DATE = convert(varchar(10), a.START_DATE, 121 ),
	END_DATE = convert(varchar(10), a.END_DATE, 121 )
from
	(
	select
		START_DATE =convert(datetime,'20040407'),
		END_DATE = convert(datetime,'20060203')
	union all
	select '20040407','20040407'  union all
	select '20040407','20050406'  union all
	select '20040407','20050407'  union all
	select '20040407',NULL  union all
	select NULL,'20050407'  union all
	select NULL,NULL  union all
	select '20040229','20060227'  union all
	select '20040229','20060228'  union all
	select '20040229','20080228'  union all
	select '20040229','20080229'  union all
	select '20060205','20060204'  union all
	select '20060205','20060205'  union all
	select '17530101 00:00:00.000','99991231 23:59:59.997'  union all
	select '19500913', getdate()
	) a

Results:


Demo Age function F_AGE_IN_YEARS
 
Age         START_DATE END_DATE   
----------- ---------- ---------- 
1           2004-04-07 2006-02-03
0           2004-04-07 2004-04-07
0           2004-04-07 2005-04-06
1           2004-04-07 2005-04-07
NULL        2004-04-07 NULL
NULL        NULL       2005-04-07
NULL        NULL       NULL
1           2004-02-29 2006-02-27
2           2004-02-29 2006-02-28
3           2004-02-29 2008-02-28
4           2004-02-29 2008-02-29
NULL        2006-02-05 2006-02-04
0           2006-02-05 2006-02-05
8246        1753-01-01 9999-12-31
56          1950-09-13 2006-11-04

(15 row(s) affected)

CODO ERGO SUM

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-17 : 10:44:40

As best I can tell, leaplings can celebrate on either Feb 28th or March 1st in the US. I haven't found a definitive federal or state law ...

Regardless, I get very good performance out of the CLR for this function. (Note: this function increments leaplings on March 1)


    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function GetAgeAtDate(Optional ByVal BirthDate As Date = Nothing, Optional ByVal EndDate As Date = Nothing) As Integer
        If BirthDate = Nothing Or _
        EndDate = Nothing Or _
        EndDate < BirthDate Then
            Return (Nothing)
        End If
        If Month(EndDate) < Month(BirthDate) Or _
        (Month(EndDate) = Month(BirthDate) And _
        (EndDate.Day) < (BirthDate.Day)) Then
            Return Year(EndDate) - Year(BirthDate) - 1
        Else
            Return Year(EndDate) - Year(BirthDate)
        End If
    End Function

EDIT: Note also ... I don't think CLR supports nullable parameters for user-defined functions, so my OPTIONAL stuff is worth nothing ...

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-20 : 08:31:17

This solutions just offered by a friend of mine ...

select (convert(int, convert(varchar, @currentdate, 112)) - convert(int, convert(varchar, @birthdate, 112)))/10000

... performs quite well.

Jay White

Kristen
Test

22859 Posts

Posted - 2006-11-20 : 08:49:36

"performs quite well"

That's a relative term I suppose, but in my experience a conversion via VARCHAR is of the order of 6 times slower:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#REPLY_ID=120953

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 10:34:04

quote:
Originally posted by Page47

This solutions just offered by a friend of mine ...

select (convert(int, convert(varchar, @currentdate, 112)) - convert(int, convert(varchar, @birthdate, 112)))/10000

... performs quite well.

Jay White

It has a few points of disagreement with my function:


select 
	[Age] = 
	(convert(int, convert(varchar, END_DATE, 112)) - convert(int, convert(varchar, START_DATE, 112)))/10000 ,
	[Age] = dbo.F_AGE_IN_YEARS( a.START_DATE, a.END_DATE ),
	[Not =] =
	case
	when (convert(int, convert(varchar, END_DATE, 112)) - convert(int, convert(varchar, START_DATE, 112)))/10000  is null and
		dbo.F_AGE_IN_YEARS( a.START_DATE, a.END_DATE ) is null
	then ''
	when (convert(int, convert(varchar, END_DATE, 112)) - convert(int, convert(varchar, START_DATE, 112)))/10000 =
		dbo.F_AGE_IN_YEARS( a.START_DATE, a.END_DATE )
	then ''
	else '<>'
	end,
	START_DATE = convert(varchar(10), a.START_DATE, 121 ),
	END_DATE = convert(varchar(10), a.END_DATE, 121 )
from
	(
	select
		START_DATE =convert(datetime,'20040407'),
		END_DATE = convert(datetime,'20060203')
	union all
	select '20040407','20040407'  union all
	select '20040407','20050406'  union all
	select '20040407','20050407'  union all
	select '20040407',NULL  union all
	select NULL,'20050407'  union all
	select NULL,NULL  union all
	select '20040229','20060227'  union all
	select '20040229','20060228'  union all
	select '20040229','20060301'  union all
	select '20040229','20080228'  union all
	select '20040229','20080229'  union all
	select '20060205','20060204'  union all
	select '20060205','20060205'  union all
	select '17530101 00:00:00.000','99991231 23:59:59.997'  union all
	select '19500913', getdate()
	) a

Results:


Age         Age         Not = START_DATE END_DATE   
----------- ----------- ----- ---------- ---------- 
1           1                 2004-04-07 2006-02-03
0           0                 2004-04-07 2004-04-07
0           0                 2004-04-07 2005-04-06
1           1                 2004-04-07 2005-04-07
NULL        NULL              2004-04-07 NULL
NULL        NULL              NULL       2005-04-07
NULL        NULL              NULL       NULL
1           1                 2004-02-29 2006-02-27
1           2           <>    2004-02-29 2006-02-28
2           2                 2004-02-29 2006-03-01
3           3                 2004-02-29 2008-02-28
4           4                 2004-02-29 2008-02-29
0           NULL        <>    2006-02-05 2006-02-04
0           0                 2006-02-05 2006-02-05
8246        8246              1753-01-01 9999-12-31
56          56                1950-09-13 2006-11-20

(16 row(s) affected)

CODO ERGO SUM

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-20 : 11:00:42

Yeah, like I said above, I'm not convince a leapling's birthday is 2/28 on non-leap years ...
Also, I'm not sure I was 0 years old, 100 years ago ... I think NULL ("UNKNOWN") may better describe that situation.

Jay White

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 06:02:38

Here is an improvement that also takes into account if time portion is present.
It also allows for "backward" calculation.

CREATE FUNCTION dbo.fnYearsApart
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS	SMALLINT
AS
BEGIN
	DECLARE	@Diff SMALLINT,
		@F DATETIME,
		@T DATETIME

	SELECT	@Diff = DATEDIFF(month, @FromDate, @ToDate) / 12,
		@F = DATEADD(year, 2000 - DATEPART(year, @FromDate), @FromDate),
		@T = DATEADD(year, 2000 - DATEPART(year, @ToDate), @ToDate)

	IF DATEDIFF(month, @FromDate, @ToDate) % 12 = 0
		BEGIN
			IF @FromDate <= @ToDate AND @F > @T 
				SELECT	@Diff = @Diff - 1

			IF @FromDate > @ToDate AND @F < @T
				SELECT	@Diff = @Diff + 1
		END

	RETURN	@Diff
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 06:24:10

[code]SELECT 'Demo Age function F_AGE_IN_YEARS and fnYearsApart'

select
[Age MVJ] = dbo.F_AGE_IN_YEARS( a.START_DATE, a.END_DATE ),
[Age Peso] = dbo.fnYearsApart( a.START_DATE, a.END_DATE ),
START_DATE = convert(varchar, a.START_DATE, 121 ),
END_DATE = convert(varchar, a.END_DATE, 121 )
from
(
select
START_DATE =convert(datetime,'20040407'),
END_DATE = convert(datetime,'20060203')
union all
select '20040407',NULL union all
select NULL,'20050407' union all
select NULL,NULL union all
select '20040229 22:17:23','20080229 14:17:22' union all
select '20040229 17:17:23','20080229 17:17:23' union all
select '19500913', getdate()
) a[/code]
Results:[code]Age MVJ Age Peso START_DATE END_DATE
1 1 2004-04-07 00:00:00.000 2006-02-03 00:00:00.000
NULL NULL 2004-04-07 00:00:00.000 NULL
NULL NULL NULL 2005-04-07 00:00:00.000
NULL NULL NULL NULL
4 3 2004-02-29 22:17:23.000 2008-02-29 14:17:22.000
4 4 2004-02-29 17:17:23.000 2008-02-29 17:17:23.000
56 56 1950-09-13 00:00:00.000 2007-02-07 12:22:51.287[/code]
Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-07 : 09:29:47

I intentionally designed F_AGE_YEARS to ignore the time portion of the input dates, and to treat a start date after the end date as invalid, so it’s working as designed.

I question the concept of negative age, so that’s why a start date after the end date returns a null.

Most people ignore time when asked for their age, and it can only come into play on one day of the year, so that’s why I made the choice to ignore time, or rather to set it to 00:00:00.00 for each date.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 09:36:35

Do you have some of your speed tests left?
To compare the nine DATEADD/DATEDIFF functions needed in your function versus the six I use.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 09:42:20

Yikes... My function is 10 percent slower!

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-07 : 10:34:38

I never did any speed testing with F_AGE_IN_YEARS.

I think I could simplify F_AGE_IN_YEARS a bit, but never really had the urge to go back to it.

A 10% difference in speed doesn't seem like much, and since the two functions have different design goals, it's not really a like to like test.

You might consider this code chnage to your function, since it would eliminate the DATEADD/DATEPART for @F and @T for most dates.


	set @Diff = DATEDIFF(month, @FromDate, @ToDate) / 12

	IF DATEDIFF(month, @FromDate, @ToDate) % 12 = 0
		BEGIN
		select	@F = DATEADD(year, 2000 - DATEPART(year, @FromDate), @FromDate),
			@T = DATEADD(year, 2000 - DATEPART(year, @ToDate), @ToDate)


			IF @FromDate <= @ToDate AND @F > @T 
				SELECT	@Diff = @Diff - 1

			IF @FromDate > @ToDate AND @F < @T
				SELECT	@Diff = @Diff + 1
		END

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 10:41:27

Thanks!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-10 : 16:49:39

Have narrowed the functions down to these two.
First the fnMonthsApart function

CREATE FUNCTION dbo.fnMonthsApart
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS	INT
AS
BEGIN
	RETURN	CASE
			WHEN @FromDate > @ToDate THEN NULL
			WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
			ELSE DATEDIFF(month, @FromDate, @ToDate)
		END
END

and then the fnYearsApart function

CREATE FUNCTION dbo.fnYearsApart
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS	INT
AS
BEGIN
	RETURN	CASE
			WHEN @FromDate > @ToDate THEN NULL
			WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN (DATEDIFF(month, @FromDate, @ToDate) - 1) / 12
			ELSE DATEDIFF(month, @FromDate, @ToDate) / 12
		END
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:44:23

Improved functions found here
http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx

E 12°55'05.63"
N 56°04'39.26"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-10-02 : 20:22:48

I know it's an old post, but I still can't believe that someone recommended the use of a CLR for this.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-03 : 16:41:27

The more I see CLR procedures used in production applications, the more I hate them.

They're a lot like triggers. Experienced SQL developers avoid them whenever possible; inexperienced developers use them as a bad practice of first choice.

CODO ERGO SUM

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-10-05 : 08:57:46

Absolutely agreed. My take is usually "CLR's and Cursors are for people who don't really know how to use T-SQL."

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources