## Date Difference in Years, Months and Days in SQL Server

Recently, I had a requirement where I had to calculate the difference in Year(s), Months(s) and Day(s) between the two dates. Unfortunately there is no in-function in SQL Server to calculate the same. So, I wrote a user-defined function for this and thought of sharing the same on my blog. So, in this post, we will see how to calculate Date difference in Years, Months and dates between two dates.

SQL Server has in-built function DATEDIFF to find the difference between 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 per our requirement. As per our requirement, it should be 0 year, 0 month and 1 day.

As a solution to above, I have created a user defined function to find the exact date difference in Year(s), Month(s) and Day(s) between two dates.
```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 test the above created function
`SELECT dbo.udfDateDiffinYrMonDay('12/31/2010','01/01/2011')`
OUTPUT
`SELECT dbo.udfDateDiffinYrMonDay('06/08/2006','01/01/2011')`
OUTPUT

Related post: calculate date difference in Year(s), Month(s) and Day(s) in C#
Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio