| Author |
 Topic  |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
 Posted - 03/05/2006 : 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 |
Edited by - Michael Valentine Jones on 11/04/2006 15:58:13
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 03/05/2006 : 02:13:13
|
Another great function from MVJ. Thanks
Works great on SQL 2005 also 
----------------------------------
'KH'
|
Edited by - khtan on 03/05/2006 02:16:53 |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/05/2006 : 10:59:09
|
"Works great on SQL 2005 also"
Cheeky monkey! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 03/06/2006 : 01:47:20
|
Good Stuff from DateTime specialist
Madhivanan
Failing to plan is Planning to fail |
|
|
|
db_sysadmin
Starting Member
27 Posts |
Posted - 04/05/2006 : 11:35:27
|
Thanks a Bunch guys! Both solutions work perfect!
|
|
|
|
triemvo
Starting Member
3 Posts |
Posted - 04/18/2006 : 00:04:55
|
chep chep, a nice script guy!
Thank!
TriemV |
|
|
|
alokeda
Starting Member
1 Posts |
Posted - 11/17/2006 : 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)
USA
7007 Posts |
Posted - 11/17/2006 : 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 - 02/12/2008 : 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 - 02/15/2008 : 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
Sweden
29156 Posts |
|
|
afsawaf
Starting Member
1 Posts |
|
|
naypui
Starting Member
Thailand
1 Posts |
Posted - 05/23/2013 : 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 |
|
|
| |
Topic |
|
|
|
Age Function F_AGE_YYYY_MM_DD
