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_YYYY_MM_DD
 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 - 03/05/2006 :  02:04:20  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 11/04/2006 15:58:13

khtan
In (Som, Ni, Yak)

Singapore
17428 Posts

Posted - 03/05/2006 :  02:13:13  Show Profile  Reply with Quote
Another great function from MVJ. Thanks
Works great on SQL 2005 also
----------------------------------
'KH'



Edited by - khtan on 03/05/2006 02:16:53
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 03/05/2006 :  10:59:09  Show Profile  Reply with Quote
"Works great on SQL 2005 also"

Cheeky monkey!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 03/06/2006 :  01:47:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 04/05/2006 :  11:35:27  Show Profile  Reply with Quote
Thanks a Bunch guys! Both solutions work perfect!

Go to Top of Page

triemvo
Starting Member

3 Posts

Posted - 04/18/2006 :  00:04:55  Show Profile  Send triemvo a Yahoo! Message  Reply with Quote
chep chep, a nice script guy!
Thank!

TriemV
Go to Top of Page

alokeda
Starting Member

1 Posts

Posted - 11/17/2006 :  15:16:13  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/17/2006 :  15:52:27  Show Profile  Reply with Quote
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 - 02/12/2008 :  06:51:28  Show Profile  Reply with Quote
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 - 02/15/2008 :  07:31:47  Show Profile  Reply with Quote
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

Sweden
29908 Posts

Posted - 02/15/2008 :  08:08:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 02/15/2008 08:10:41
Go to Top of Page

afsawaf
Starting Member

1 Posts

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

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

Go to Top of Page

naypui
Starting Member

Thailand
1 Posts

Posted - 05/23/2013 :  23:16:12  Show Profile  Reply with Quote
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
  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.12 seconds. Powered By: Snitz Forums 2000