Technical Article

Accurate Time between Two Dates in Year, Month,Day Format

,

This is a function, please follow the comments on script to use it.

/****** Object:  UserDefinedFunction [dbo].[fn_TotaltimeBetweendates]    Script Date: 7/10/2014 9:43:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Created By Vimal Lohani
--Modified By ChrisM@Work
--Select [dbo].[fn_TotaltimeBetweendates] (Getdate(), '06/13/1990' )
--Select [dbo].[fn_TotaltimeBetweendates] ('06/13/1990', GetDate())
alter Function [dbo].[fn_TotaltimeBetweendates] (@Fromdate datetime, @Todate datetime )
Returns nvarchar(40)
as
Begin
if(@Fromdate>@Todate)
		begin
			Declare @Tempvar datetime=@Fromdate
					set @Fromdate=@Todate
					set @Todate=@Tempvar
		end
return (SELECT 
	CAST(Years AS VARCHAR(4)) + ' Years :' + CAST(Months AS VARCHAR(2)) + ' Months :' + CAST([Days] AS VARCHAR(2)) + ' Days'
FROM ( -- f
	SELECT 
		Years, Months, [Days] = DATEDIFF(DAY,DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate)),@Todate) 
			- CASE WHEN DATEADD(DAY,DATEDIFF(DAY,DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate)),@Todate),DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate))) > @Todate THEN 1 ELSE 0 END  
	FROM ( -- e
		SELECT Years, [Months] = DATEDIFF(MONTH,DATEADD(YEAR,Years,@Fromdate),@Todate) 
			- CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH,DATEADD(YEAR,Years,@Fromdate),@Todate),DATEADD(YEAR,Years,@Fromdate)) > @Todate THEN 1 ELSE 0 END
		FROM ( -- d
			SELECT 
				[Years] = DATEDIFF(YEAR,@Fromdate,@Todate) - CASE WHEN DATEADD(YEAR,DATEDIFF(YEAR,@Fromdate,@Todate),@Fromdate) > @Todate THEN 1 ELSE 0 END
		) d
	) e
) f )
End

Rate

3.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

3.86 (7)

You rated this post out of 5. Change rating