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)

TSQL get date difference as string
Tagged on:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.