TSQL get date difference as string

Home

Articles

SQL/SQL Server Articles

Wednesday, 10 August 2011 15:20 |

Written by Sam Jenkins |

|

|

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. After spending quite some time looking around the Internet to try and find something that would work, I didn't find anything that suited my needs so 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)

Last Updated (Wednesday, 10 August 2011 15:35)

Satal Keto