/*----------------------------------------------------------------------------------------------------------------------------
  Author 	:-	ANAND BABU UCHAGAWKAR
  Purpose	:-	To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)).				
  DATE		:-	30-Aug-2011
  DATABASE	:- 	SQL

----------------------------------------------------------------------------------------------------------------------------*/
IF (Select COUNT(*) From Sysobjects Where [name] like 'FN_GETDATEDIFFTEXT') > 0
BEGIN
	DROP FUNCTION FN_GETDATEDIFFTEXT
END
GO
CREATE FUNCTION FN_GETDATEDIFFTEXT(@FromDate DateTime, @ToDate DateTime) 
RETURNS NVARCHAR(50)
AS
BEGIN	

	Declare @daysDiff Int
	Declare @monthDiff Int
	Declare @yearDiff Int

	--Select @daysDiff = DATEDIFF(DAY, @FromDate, @ToDate)
	Set @monthDiff = ABS(DATEDIFF(MONTH, @FromDate, @ToDate)%12)
	Set @yearDiff = ABS(DATEDIFF(YYYY, @FromDate, @ToDate))

	-- If the From date month is greater than the month of the To date and the year difference is greater than zero
	-- then the year should the deducted by one
	IF DATEPART(MONTH,@FromDate) > DATEPART(MONTH,@ToDate) AND @yearDiff > 0
	BEGIN
		Set @yearDiff = @yearDiff - 1
	END

	IF DATEPART(DAY,@FromDate) > DATEPART(DAY, @ToDate)
	Begin
		--Get last date of the month of the FromDate
		Declare @lastDateOfMonth DateTime = DATEADD(MONTH, 1, @FromDate)	
		Set @lastDateOfMonth = '01-' + DATENAME(MONTH,@lastDateOfMonth) + '-'+DATENAME(YEAR,@lastDateOfMonth)
		Set @lastDateOfMonth = DATEADD(DAY, -1, @lastDateOfMonth)
		
		Set @daysDiff = DATEDIFF(DAY, @FromDate, @lastDateOfMonth)
		Set @daysDiff = @daysDiff + DATEPART(DAY, @ToDate)
		Set @monthDiff = @monthDiff - 1
	End
	ELSE
	BEGIN
		Set @daysDiff = DATEPART(DAY, @ToDate) - DATEPART(DAY, @FromDate)
	END

	-- Select @yearDiff Yr, @monthDiff Mn, @daysDiff Dy
	RETURN 
		CAST(@yearDiff as nvarchar) + ' year(s), ' + 
		CAST(@monthDiff as  nvarchar) + ' month(s), ' + 
		CAST(@daysDiff as nvarchar) + ' day(s)'
END
GO

 Select DBO.FN_GETDATEDIFFTEXT('2011-01-16', '2013-01-01')
-- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())

select
        [Days]          = datediff(day,0,ET-ST)-1,
        [Hours]         = datepart(Hour,ET-ST)-1,
        [Minutes]       = datepart(Minute,ET-ST)-1,
        [Seconds]       = datepart(Second,ET-ST),
        [Milliseconds]  = datepart(millisecond,ET-ST)
from
        (
        select  -- Test Data
                ST = convert(datetime,'2008/09/22 00:35:33.997'),
                ET = convert(datetime,'2009/10/22 04:05:45.443')
        ) a

select  [Years]        = datediff(year,0,ET-ST)-1,
        [Months]        = datepart(month,ET-ST)-1,
        [Days]          = datepart(day,ET-ST)-1,
        [Hours]         = datepart(Hour,ET-ST),
        [Minutes]       = datepart(Minute,ET-ST),
        [Seconds]       = datepart(Second,ET-ST),
        [Milliseconds]  = datepart(millisecond,ET-ST)
from
        (
        select  -- Test Data
                ST = convert(datetime,'2008/09/22 00:35:33.997'),
                ET = convert(datetime,'2009/10/23 04:05:45.443')
        ) a

select  [Years]        = datediff(year,0,ET-ST)-1,
        [Months]        = datepart(month,ET-ST)-1,
        [Days]          = datepart(day,ET-ST)-1
from
        (
        select  -- Test Data
                ST = convert(datetime,'2011/01/01'),
                ET = convert(datetime,'2013/01/13')
        ) a

declare @date1 date = '2010-05-17'
declare @date2 date = '2013-01-16'
declare @years int
declare @months int
declare @days int

select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = case when @months >= 0 then @months else 12 + @months end
select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)
select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end

select @years as [@years], @months as [@months], @days as [@days]

DECLARE @FromDate DateTime, @ToDate DateTime
SELECT @FromDate = '2011-01-16', @ToDate = '2013-01-01'

DECLARE @RefDate datetime
,	@dayDiff Int
,	@monthDiff Int
,	@yearDiff Int


IF @FromDate > @ToDate
SELECT @RefDate = @FromDate, @FromDate = @ToDate, @ToDate = @RefDate

SELECT @RefDate = DateAdd(Year, DateDiff(Year, @FromDate, @ToDate), @FromDate)
,	@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Year, -1, @RefDate) ELSE @RefDate END
,	@yearDiff = DateDiff(Year, @FromDate, @RefDate)
,	@FromDate = @RefDate
,	@RefDate = DateAdd(Month, DateDiff(Month, @FromDate, @ToDate), @FromDate)
,	@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Month, -1, @RefDate) ELSE @RefDate END
,	@monthDiff = DateDiff(Month, @FromDate, @RefDate)
,	@FromDate = @RefDate
,	@RefDate = DateAdd(Day, DateDiff(Day, @FromDate, @ToDate), @FromDate)
,	@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Day, -1, @RefDate) ELSE @RefDate END
,	@dayDiff = DateDiff(Day, @FromDate, @RefDate)

SELECT @yearDiff, @monthDiff, @dayDiff