Technical Article

Function to calculate and return age in text to closest minute between

,

 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

Rate

3 (7)

Share

Share

Rate

3 (7)