``````CREATE FUNCTION dbo.GetDuration(@FromDt as datetime)
returns varchar(255)
as
BEGIN
DECLARE @YRS AS SMALLINT, @MNT AS SMALLINT, @DY AS SMALLINT, @Fvalue varchar(255)

SET @YRS = DATEDIFF(YY, @FromDt,GETDATE())
SET @MNT = DATEDIFF(MM, @FromDt,GETDATE()) % 12

SET @DY = DATEDIFF(DD,GETDATE(), DATEADD(MM, (@YRS  12) + @MNT,@FromDt) )

SET @Fvalue = CAST(@YRS AS VARCHAR) + ' YEARS ' + CAST(@MNT AS VARCHAR) + ' MONTH ' + CAST(@DY AS VARCHAR) + ' DAYS '

RETURN @Fvalue``````

Edited by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

2
Contributors
1
2
Views
8 Years
Discussion Span
Last Post by coryloriot

OK, you didn't have the multiplier in your day formula to multiply the years by 12. might have been a typo, and then you also needed to make the day value positive. so I just threw in a multiply by -1 at the end. Hope that helps! Oh, and you didn't put in the END keyword.

``````CREATE FUNCTION dbo.GetDuration(@FromDt as datetime)
returns varchar(255)
as
BEGIN
DECLARE @YRS AS SMALLINT, @MNT AS SMALLINT, @DY AS SMALLINT, @Fvalue varchar(255)

SET @YRS = DATEDIFF(YY, @FromDt,GETDATE())
SET @MNT = DATEDIFF(MM, @FromDt,GETDATE()) % 12

SET @DY = (DATEDIFF(DD,GETDATE(), DATEADD(MM, (@YRS * 12) + @MNT,@FromDt) )*-1)

SET @Fvalue = CAST(@YRS AS VARCHAR) + ' YEARS ' + CAST(@MNT AS VARCHAR) + ' MONTH ' + CAST(@DY AS VARCHAR) + ' DAYS '

RETURN (@Fvalue)
END``````

I ran this and it worked:

``````declare @Epoch datetime
set @Epoch = '1/1/1971'

select @Epoch as [Epoch]

select dbo.GetDuration(@Epoch) as [Time Since Epoch]``````
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.