Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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
22858 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
22864 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
30421 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  
 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.76 seconds. Powered By: Snitz Forums 2000