Year, Month, and Day Difference Display in SQL Server

[Hashigato]

We need to display values between two dates in the format of A years, B months, C days in your applications. We have implemented a simple application to achieve this display. When performing database operations with date data, we require some methods or functions for a more aesthetic representation of date values. Let us briefly share that function with you.

Our function requires two date values as input. After processing the given two dates, it returns the representation as, for example, '2 Years 7 Months 16 Days'.

```sql
CREATE FUNCTION dbo.fnYearMonthDay ( @StartDate DATETIME, @EndDate DATETIME )
RETURNS varchar(100)
AS
BEGIN
DECLARE @Years INT
DECLARE @Months INT
DECLARE @Days INT
DECLARE @Result VARCHAR(100)
SET @Years = DATEDIFF(yy, @StartDate, @EndDate)
SET @Months = DATEDIFF(mm, @StartDate, @EndDate) - (@Years * 12)
SET @Days = DATEDIFF(dd, @StartDate, @EndDate) - ((@Years * 365) + (@Months * 30))
IF (@Years BEGIN SET @Years = '' END
IF (@Months BEGIN SET @Months = '' END
IF (@Days BEGIN SET @Days = '' END
SET @Result = (' ' + CAST(@Years AS VARCHAR(4)) + ' Years ' + CAST(@Months AS VARCHAR(2)) + ' Months ' + CAST(@Days AS VARCHAR(2)) + ' Days')
SET @Result = REPLACE(@Result, ' 0 Years', '')
SET @Result = REPLACE(@Result, ' 0 Months', '')
SET @Result = REPLACE(@Result, ' 0 Days', '')
RETURN @Result
END
```

Now that our function is written, let's confirm its existence in SQL Server.

Example 1 of the Function Usage:
```sql
SELECT dbo.fnYearMonthDay('2012-01-02', '2020-03-10') AS DateDifference
```
Example 1 Result

Example 2 of the Function Usage:
```sql
SELECT dbo.fnYearMonthDay('2018-04-20', '2018-05-28') AS DateDifference
```
Example 2 Result

Happy querying days!
Source: [https://www.ontedi.com/sql/sql-serv...l/sql-serverda-yil-ay-ve-gun-farki-gosterimi)