Age Function F_AGE_YYYY_MM_DD - 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_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'

Kristen
Test

22859 Posts

Posted - 2006-03-05 : 10:59:09

"Works great on SQL 2005 also"

Cheeky monkey!

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

db_sysadmin
Starting Member

27 Posts

Posted - 2006-04-05 : 11:35:27

Thanks a Bunch guys! Both solutions work perfect!

triemvo
Starting Member

3 Posts

Posted - 2006-04-18 : 00:04:55

chep chep, a nice script guy!
Thank!

TriemV

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')

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

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

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

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"

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources