| Author |
 Topic  |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
 Posted - 11/04/2006 : 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 |
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
|
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 |
 |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/20/2006 : 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
United Kingdom
22191 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/20/2006 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/20/2006 : 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
Sweden
29138 Posts |
Posted - 02/07/2007 : 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 |
Edited by - SwePeso on 02/07/2007 06:15:50 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/07/2007 : 06:24:10
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/07/2007 : 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
Sweden
29138 Posts |
Posted - 02/07/2007 : 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 |
Edited by - SwePeso on 02/07/2007 09:46:53 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/07/2007 : 09:42:20
|
Yikes... My function is 10 percent slower!
Peter Larsson
Helsingborg, Sweden |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/07/2007 : 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
Sweden
29138 Posts |
Posted - 02/07/2007 : 10:41:27
|
Thanks!
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/10/2007 : 16:49:39
|
Have narrowed the functions down to these two.
First the fnMonthsApart functionCREATE 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 functionCREATE 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
Sweden
29138 Posts |
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 10/02/2009 : 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)
USA
6997 Posts |
Posted - 10/03/2009 : 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
USA
643 Posts |
Posted - 10/05/2009 : 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"
|
|
|
| |
Topic |
|
|
|
Age Function F_AGE_IN_YEARS
