Saturday, July 7, 2012

Getting Age in Text or Date Difference in text format

/*----------------------------------------------------------------------------------------------------------------------------
  Author     :-    Alok Kumar Sharma
  Purpose    :-    To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)).              
  DATE        :-   07-July-2012
  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('30-Dec-2010', '31-Jan-2011')
-- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())

No comments:

Post a Comment