Member Avatar for qasimidl 
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 kvprajapati because: Added [code] tags. Encase your code in: [code] and [/code] tags.
  • 2 Contributors
  • 1 Reply
  • 65 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by coryloriot
Member Avatar for 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]
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.