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_YYYY_MM_DD

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-05 : 02:04:20
This function calculates age in years, months and days from @START_DATE through @END_DATE and returns the age in format YYYY MM DD.

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

Months is the number of full months since the last full year anniversary.

Days is the number of days since the last full month anniversary.

I have seen a number of questions posted on SQLTeam about calculating age in years, months, and days, and it is definitely not a trivial algorithm, so I developed this function to show how to do it.

This format may not be convenient for all applications, but I wanted a scalar function, so I returned it as a VARCHAR(10) value in format YYYY MM DD. If your application requires a different format, it is fairly easy to modify for the format you need.


This function is a companion to the function on the following link that calculates Age to the precision of days.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462



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



drop function dbo.F_AGE_YYYY_MM_DD
go
create function dbo.F_AGE_YYYY_MM_DD
(
@START_DATE datetime,
@END_DATE datetime
)
returns varchar(10)
as
/*
Function: F_AGE_YYYY_MM_DD

This function calculates age in years, months and days
from @START_DATE through @END_DATE and
returns the age in format YYYY MM DD.

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

Months is the number of full months since the last full year anniversary.

Days is the number of days since the last full month anniversary.

*/
begin

declare @AGE varchar(10)

declare @AGE_IN_YEARS int
declare @AGE_IN_MONTHS int
declare @AGE_IN_DAYS int

-- Return null if @START_DATE > @END_DATE
if @START_DATE > @END_DATE begin return @AGE end

select
@AGE_IN_YEARS = AGE_IN_YEARS,
@AGE_IN_MONTHS = AGE_IN_MONTHS,
@AGE_IN_DAYS =
datediff(dd,
dateadd(mm,AGE_IN_MONTHS,
dateadd(yy,AGE_IN_YEARS,START_DATE))
,END_DATE)
from
(
select
AGE_IN_MONTHS =
case
when AnniversaryThisMonth <= END_DATE
then datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
else datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)-1
end,
*
from
(
select
AGE_IN_YEARS =
case
when AnniversaryThisYear <= END_DATE
then datediff(yy,START_DATE,END_DATE)
else datediff(yy,START_DATE,END_DATE)-1
end,
*
from
(
select
AnniversaryThisYear =
dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth =
dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE),
*
from
(
select START_DATE = dateadd(dd,datediff(dd,0,@START_DATE),0),
END_DATE = dateadd(dd,datediff(dd,0,@END_DATE),0)
) aaaa
) aaa
) aa
) a

select @AGE =
right('0000'+convert(varchar(4),@AGE_IN_YEARS),4) + ' ' +
right('00'+convert(varchar(4),@AGE_IN_MONTHS),2) + ' ' +
right('00'+convert(varchar(4),@AGE_IN_DAYS),2)

return @AGE

end
go

select [Age] = dbo.F_AGE_YYYY_MM_DD('2004-04-07','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-03','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-05','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('1950-09-13', getdate())

Results:

Age
----------
0001 09 27

(1 row(s) affected)

Age
----------
0000 00 00

(1 row(s) affected)

Age
----------
NULL

(1 row(s) affected)

Age
----------
0055 05 20

(1 row(s) affected)



CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 02:13:13
Another great function from MVJ. Thanks
Works great on SQL 2005 also
----------------------------------
'KH'


Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-03-05 : 10:59:09
"Works great on SQL 2005 also"

Cheeky monkey!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 01:47:20
Good Stuff from DateTime specialist

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

db_sysadmin
Starting Member

27 Posts

Posted - 2006-04-05 : 11:35:27
Thanks a Bunch guys! Both solutions work perfect!

Go to Top of Page

triemvo
Starting Member

3 Posts

Posted - 2006-04-18 : 00:04:55
chep chep, a nice script guy!
Thank!

TriemV
Go to Top of Page

alokeda
Starting Member

1 Post

Posted - 2006-11-17 : 15:16:13
Great function but fails in the following dates:
select [Age] = dbo.F_AGE_YYYY_MM_DD('2000-02-29','2004-02-28')
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-17 : 15:52:27
quote:
Originally posted by alokeda

Great function but fails in the following dates:
select [Age] = dbo.F_AGE_YYYY_MM_DD('2000-02-29','2004-02-28')



No it doesn't.

I produces the correct result of 3 years 11 months and 31 days.

CODO ERGO SUM
Go to Top of Page

pdreyer
Starting Member

6 Posts

Posted - 2008-02-12 : 06:51:28
quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by alokeda

Great function but fails in the following dates:
select [Age] = dbo.F_AGE_YYYY_MM_DD('2000-02-29','2004-02-28')



No it doesn't.

I produces the correct result of 3 years 11 months and 31 days.

CODO ERGO SUM


No that is incorrect.
It should be 3 years 11 months and 30 days
Your day count didn't start at 0
see below

select fromdt, todt ,dbo.F_AGE_YYYY_MM_DD(fromdt,todt) age
from
(
select convert(datetime,'20000229') fromdt,
dateadd(dd,number,'20040127') todt
from master..spt_values
where type='P'
and number<40
) view1

fromdt todt age
----------------------- ----------------------- ----------
2000-02-29 00:00:00.000 2004-01-27 00:00:00.000 0003 10 30
2000-02-29 00:00:00.000 2004-01-28 00:00:00.000 0003 10 31
2000-02-29 00:00:00.000 2004-01-29 00:00:00.000 0003 11 01
2000-02-29 00:00:00.000 2004-01-30 00:00:00.000 0003 11 02
...
2000-02-29 00:00:00.000 2004-02-27 00:00:00.000 0003 11 30
2000-02-29 00:00:00.000 2004-02-28 00:00:00.000 0003 11 31
2000-02-29 00:00:00.000 2004-02-29 00:00:00.000 0004 00 00
2000-02-29 00:00:00.000 2004-03-01 00:00:00.000 0004 00 01
Go to Top of Page

pdreyer
Starting Member

6 Posts

Posted - 2008-02-15 : 07:31:47
Here is a function that also use the time

create function dbo.age (@fromdt datetime, @todt datetime)
returns varchar(42) as
begin
declare @d datetime, @sgn char(1), @i int
if @fromdt>@todt
select @d=@fromdt, @fromdt=@todt, @todt=@d, @sgn='-' --swap dates
else
set @sgn=''
select @todt=case when @fromdt>dateadd(dd,-datediff(dd,@fromdt,@todt),@todt)
then dateadd(dd,-1,@todt) else @todt end
,@i=case when datepart(dd,@todt)<datepart(dd,@fromdt) then 1 else 0 end
return ( select @sgn
+convert(varchar(4),(datediff(mm,@fromdt,@todt)-@i)/12)+' Years '
+convert(varchar(2),(datediff(mm,@fromdt,@todt)-@i)%12)+' Months '
+convert(varchar(2),datediff(dd,dateadd(mm,
(datediff(mm,@fromdt,@todt)-@i),@fromdt),@todt))+' Days '
+right(convert(char(23)
,dateadd(ms,datediff(ms,@fromdt,dateadd(dd,-datediff(dd,@fromdt,@todt),@todt)),0)
,21),12)
)
end
go

-- e.g. --

select fromdt, todt, dbo.age(fromdt,todt) age
from ( select
'2000-02-29 06:10:05.003','2004-02-29 06:10:05.000' union all select
'2000-02-29 06:10:05.003','2004-02-29 06:10:05.003' union all select
'2000-02-29 06:10:05.003','2004-02-29 06:10:05.006'
)t(fromdt,todt)
order by 2

fromdt todt age
----------------------- ----------------------- ------------------------------------------
2000-02-29 06:10:05.003 2004-02-29 06:10:05.000 3 Years 11 Months 30 Days 23:59:59.997
2000-02-29 06:10:05.003 2004-02-29 06:10:05.003 4 Years 0 Months 0 Days 00:00:00.000
2000-02-29 06:10:05.003 2004-02-29 06:10:05.006 4 Years 0 Months 0 Days 00:00:00.003


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 08:08:58
Anyone of these two works as expected
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78730


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

afsawaf
Starting Member

1 Post

Posted - 2012-08-21 : 10:55:55
Thanks a lot for the effort, I have a modified version from this one on my blog, of course with your acknoledgment! :)

[url]http://afsawaf.blogspot.com/2012/08/calculate-age-using-tsql.html[/url]

Go to Top of Page

naypui
Starting Member

1 Post

Posted - 2013-05-23 : 23:16:12
try this

CREATE function [dbo].[age_YYYY_MM_DD]
(
@DocuDate1 date , @DocuDate2 date
)
returns varchar(10)
as
begin
declare @Result as varchar(10)
if(@DocuDate1 > @DocuDate2)
begin
set @Result = NULL

goto stop
end
declare @Year as int
declare @Month as int
declare @Day as int

set @DocuDate2 = DATEADD(D,1,@DocuDate2)

set @Year = DATEDIFF(Year,@DocuDate1,@DocuDate2)
set @Month = DATEDIFF(Month,DATEADD(Year,@Year,@DocuDate1),@DocuDate2)

if(@Month < 0)
begin
set @Year = DATEDIFF(Year,@DocuDate1,@DocuDate2)-1
set @Month = DATEDIFF(Month,DATEADD(Year,@Year,@DocuDate1),@DocuDate2)
end

select @Day = DATEDIFF(Day,DATEADD(Month,@Month,DATEADD(Year,@Year,@DocuDate1)),@DocuDate2)

if(@Day < 0)
begin
set @Month = DATEDIFF(Month,DATEADD(Year,@Year,@DocuDate1),@DocuDate2)-1
set @Day = DATEDIFF(Day,DATEADD(Month,@Month,DATEADD(Year,@Year,@DocuDate1)),@DocuDate2)
end

set @Result = REPLACE(STR(@Year,4),' ','0')+'_'+REPLACE(STR(@Month,2),' ','0')+'_'+REPLACE(STR(@Day,2),' ','0')
stop:
return @Result
end
Go to Top of Page
   

- Advertisement -