SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Age Function F_AGE_IN_YEARS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/04/2006 :  15:53:56  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 11/04/2006 15:55:53

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/17/2006 :  10:44:40  Show Profile  Reply with Quote
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

Edited by - Page47 on 11/17/2006 10:45:44
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/20/2006 :  08:31:17  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/20/2006 :  08:49:36  Show Profile  Reply with Quote
"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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/20/2006 :  10:34:04  Show Profile  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/20/2006 :  11:00:42  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/07/2007 :  06:02:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 02/07/2007 06:15:50
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/07/2007 :  06:24:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Results:
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

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 02/07/2007 06:50:54
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/07/2007 :  09:29:47  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/07/2007 :  09:36:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 02/07/2007 09:46:53
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/07/2007 :  09:42:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yikes... My function is 10 percent slower!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/07/2007 :  10:34:38  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/07/2007 :  10:41:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/10/2007 :  16:49:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/16/2009 :  10:44:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 10/02/2009 :  20:22:48  Show Profile  Reply with Quote
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"

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/03/2009 :  16:41:27  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 10/05/2009 :  08:57:46  Show Profile  Reply with Quote
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"

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000