SQL - Calculate time difference in Minutes, Hours, Days, Weeks, Months, Years for Posts / Notification

In this post I will show you how you can easily calculate time difference between two dates in seconds, minutes, hours, days, and even weeks, months and years in SQL.

This functionality can be used in notifications, emails, blog post etc

The key of this calculation is in Modulo operator, %. It returns the remainder (NOT the result!) of one number divided by another!

CREATE FUNCTION [dbo].[FN_GetTimeDifference] (@FromDate DATETIME, @ToDate DATETIME)      
RETURNS NVARCHAR(50)  
AS       
BEGIN       

DECLARE @Result NVARCHAR(50)       

SELECT  @Result = CASE WHEN DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 / 24 / 7 > 0 THEN CAST(DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks ago' 
   WHEN DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 / 24 % 7 > 0 THEN CAST(DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days ago'
   WHEN DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 % 24 > 0 THEN CAST(DATEDIFF(second, @FromDate, @ToDate) / 60 / 60 % 24  AS NVARCHAR(50)) + ' hrs ago'
   WHEN DATEDIFF(second, @FromDate, @ToDate) / 60 % 60 > 0 THEN CAST(DATEDIFF(second, @FromDate, @ToDate) / 60 % 60 AS NVARCHAR(50)) + ' min ago'
   WHEN DATEDIFF(second, @FromDate, @ToDate) % 60 > 0 THEN CAST(DATEDIFF(second, @FromDate, @ToDate) % 60 AS NVARCHAR(50)) + ' sec ago' 
   END 

 
  
RETURN  @Result     

END  

Following is the usage and the expected output of the above function. In most of the cases we need to use GETDATE() as @ToDate parameter.

-----------------------------------------------------------------------------
SELECT dbo.FN_GetTimeDifference('2017-05-01 11:58:00', '2017-05-01 11:58:08')  
8 sec ago 
-----------------------------------------------------------------------------
SELECT dbo.FN_GetTimeDifference('2017-05-01 11:30:00', '2017-05-01 11:58:08')  
28 min ago
-----------------------------------------------------------------------------
SELECT dbo.FN_GetTimeDifference('2017-05-01 02:58:00', '2017-05-01 11:58:08')  
9 hrs ago
-----------------------------------------------------------------------------
SELECT dbo.FN_GetTimeDifference('2017-04-30 11:58:00', '2017-05-01 11:58:08')  
1 days ago
-----------------------------------------------------------------------------
SELECT dbo.FN_GetTimeDifference('2017-04-15 11:58:00', '2017-05-01 11:58:08')  
2 weeks ago
-----------------------------------------------------------------------------