Tuesday, June 7, 2011

Date Difference in Years, Months and Days

Sql Server has provided the function DATEDIFF to find the difference between the two dates, but, it works only on datepart provided in the first parameter of the function.

Let's take a look how it works

DECLARE @date1 DATETIME, @date2 DATETIME
SET @date1='12/31/2010'
SET @date2='01/01/2011'
SELECT    datediff(YEAR,@date1,@date2) as years
        , datediff(MONTH,@date1,@date2) as months
        , datediff(DAY,@date1,@date2) as days
OUTPUT
The actual difference between the two dates in only 1 day but look at the output it returns 1 year, 1 month and 1 day, which is incorrect.

As a solution to above, I have created a user defined function to find the exact date difference in Years, Months and Days.

CREATE FUNCTION [dbo].[udfDateDiffinYrMonDay] (@datefrom DATETIME, @dateto DATETIME)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT
    SET @Years = DATEDIFF(YEAR, @datefrom, @dateto)
    IF DATEADD(YY, @Years, @datefrom) > @dateto
    BEGIN
        SET @Years = @Years - 1
    END
    SET @datefrom = DATEADD(YY, @Years, @datefrom)
    
    SET @Months = DATEDIFF(MM, @datefrom, @dateto)
    IF DATEADD(MM, @Months, @datefrom) > @dateto
    BEGIN
        SET @Months = @Months - 1
    END
    SET @datefrom = DATEADD(MM, @Months, @datefrom)
    
    SET @Days = DATEDIFF(DD, @datefrom, @dateto)
    
    RETURN CAST(@Years AS VARCHAR) + ' Years '
            + CAST(@Months AS VARCHAR) + ' Months '
            + CAST(@Days AS VARCHAR) + ' Days'
END

Let's check out the above created function

SELECT dbo.udfDateDiffinYrMonDay('06/08/2006','01/01/2011')
OUTPUT


Refer this link to calculate date difference (in Years, Months and Days) in C#

0 comments:

Post a Comment