SELECT [dbo].[fn_TxtAge] ('1969-01-05 10:30:20',getdate())
-- or
select name,
[dbo].[fn_TxtAge] (crdate, getdate()) as Age
from sysobjects
SELECT [dbo].[fn_TxtAge] ('1969-01-05 10:30:20',getdate())
-- or
select name,
[dbo].[fn_TxtAge] (crdate, getdate()) as Age
from sysobjects
create function [dbo].[fn_TxtAge] (@fromdatetime datetime, @todatetime datetime)
/**
Calculates and returns age in text to closest minute between 2 dates, eg: 2 Years, 4 Months, 12 Days, 4 Hours, 17 Minutes
Usageseage sample: SELECT [dbo].[fn_TxtAge] ('1969-07-08 10:30:20',getdate())
Created by Shane Clarke
**/returns varchar(200) as
begin
declare @d datetime, @PNSign char(1), @DayInt int
if @fromdatetime>@todatetime
select @d=@fromdatetime, @fromdatetime=@todatetime, @todatetime=@d, @PNSign='-'
else
set @PNSign=''
select @todatetime= case when @fromdatetime>dateadd(dd,-datediff(dd,@fromdatetime,@todatetime),@todatetime)
then dateadd(dd,-1,@todatetime)
else @todatetime
end
,@DayInt=case when datepart(dd,@todatetime)<datepart(dd,@fromdatetime) then 1 else 0 end
return ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(( select @PNSign
+' '
+convert(varchar(4),(datediff(m,@fromdatetime,@todatetime)-@DayInt)/12)+' Years, '
+convert(varchar(2),(datediff(m,@fromdatetime,@todatetime)-@DayInt)%12)+' Months, '
+convert(varchar(2),datediff(d,dateadd(mm,(datediff(mm,@fromdatetime,@todatetime)-@DayInt),@fromdatetime),@todatetime))+' Days, '
+convert(varchar(2),convert(int,left(right(convert(varchar(23),dateadd(ms,datediff(ms,@fromdatetime,dateadd(dd,-datediff(dd,@fromdatetime,@todatetime),@todatetime)),0),21),12),2))) + ' Hours, '
+convert(varchar(2),convert(int,substring(right(convert(varchar(23),dateadd(ms,datediff(ms,@fromdatetime,dateadd(dd,-datediff(dd,@fromdatetime,@todatetime),@todatetime)),0),21),12),4,2))) + ' Minutes'
)
,' 0 Years, ',' ' ),' 0 Months, ',' ' ),' 0 Days, ',' ' ),' 0 Hours, ',' ' ),', 0 Minutes','' ), ' 1 Years','1 Year' ),' 1 Months',' 1 Month' ),' 1 Days',' 1 Day' ),' 1 Hours',' 1 Hour' ),', 1 Minutes',', 1 Minute' )
))
end