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)

Share

Share

Rate

3.86 (7)