SQL - Calculating time difference in Minutes, Hours, Days, Weeks, Months, Years

Introduction

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

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

Simple example

The following example divides the number 38 by 5. This results in 7 as the integer portion of the result, and demonstrates how modulo returns the remainder of 3.

SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder;




Integer     Remainder

----------- -----------

7           3

 

(1 row(s) affected)




We will calculate the difference between two dates: ARTICLE created date - "2012-04-01 08:10:16", and NOW datetime which we will be set as variable - "2012-04-10 11:35:36". 

In real world instead of last date, you would probably use GETDATE() SQL function, that returns real datetime. 

DECLARE @articleDT DATETIME;

DECLARE @nowDate DATETIME;

 

-- Time of the ARTICLE created

SET @articleDT = '2012-04-01 08:10:16';

 

-- Simulation of NOW datetime

-- (in real world you would probably use GETDATE())

SET @nowDate = '2012-04-10 11:35:36';

 

-- Created 9 days ago.

SELECT 'Created ' + CAST(DATEDIFF(day, @articleDT, @nowDate) AS NVARCHAR(50)) + ' days ago.';

 

-- Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.

SELECT 'Created '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 % 24  AS NVARCHAR(50)) + ' hours, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 % 60 AS NVARCHAR(50)) + ' minutes and '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) % 60 AS NVARCHAR(50)) + ' seconds ago.';

          

As you can see, this algorithm will give you very useful text-like information about how much time has passed since the article was created.

--------------------------------------------------------------------

Created 9 days ago.

 

(1 row(s) affected)

 

 

----------------------------------------------------------------------------------------------

Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.

 

(1 row(s) affected)

          

This functionality is also very useful when you must display the time in format like: 

"News posted few hours ago..."
"Article posted two weeks ago..."
"Article posted six months ago...",
etc...