I found myself recently needing to be able to present the user with the difference between two dates as a string, unfortunately the data had to come straight from SQL Server into the application that they was using ready to be displayed. After spending quite some time looking around the Internet to try and find a TSQL function that would work, I came up with my own and decided to share it with you.
The following creates a function on the SQL Server database;
CREATE FUNCTION dbo.DateDiffAsString (@STARTDATE AS DATETIME, @ENDDATE AS DATETIME)
RETURNS VARCHAR(30)
BEGIN
DECLARE @TEMPDATE DATETIME, @YEARS INT, @MONTHS INT, @DAYS INT, @RTN VARCHAR(30)
SELECT @TEMPDATE = @STARTDATE
SELECT @YEARS = DATEDIFF(yy, @TEMPDATE, @ENDDATE) - CASE WHEN (MONTH(@STARTDATE) > MONTH(@ENDDATE)
OR (MONTH(@STARTDATE) = MONTH(@ENDDATE)
AND DAY(@STARTDATE) > DAY(@ENDDATE))) THEN 1 ELSE 0 END
SELECT @TEMPDATE = DATEADD(yy, @YEARS, @TEMPDATE)
SELECT @MONTHS = DATEDIFF(m, @TEMPDATE, @ENDDATE) - CASE WHEN (DAY(@STARTDATE) > DAY(@ENDDATE)) THEN 1 ELSE 0 END
SELECT @TEMPDATE = DATEADD(m, @MONTHS, @TEMPDATE)
SELECT @DAYS = DATEDIFF(d, @TEMPDATE, @ENDDATE)
SELECT @RTN = CASE WHEN @YEARS > 0 THEN CAST(@YEARS AS VARCHAR(3)) + CASE WHEN @YEARS > 1 THEN ' years 'ELSE ' year ' END ELSE '' END
+ CAST(@MONTHS AS VARCHAR(2)) + CASE WHEN @MONTHS <> 1 THEN ' months ' ELSE ' month ' END
+ CAST(@DAYS AS VARCHAR(2)) + CASE WHEN @DAYS <> 1 THEN ' days' ELSE ' day' END
RETURN(@RTN)
END
And you would call it like this;
SELECT dbo.DateDiffAsString('2010-06-03 00:00:00.000', GETDATE()) AS Diff
Which will return something like “1 year 5 months 4 days” (obviously depending on when you run it)