Calculating time difference in Minutes Hours Days Weeks Months Years

Calculate time difference like facebook messaging system in sql

DECLARE @articleDT DATETIME;
DECLARE @nowDate DATETIME;

-- Time of the ARTICLE created
SET @articleDT = '2015-01-01 08:10:16';

-- Simulation of NOW datetime
-- (in real world you would probably use GETDATE())
SET @nowDate = '2016-05-10 11:35:36';

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

-- Created 1 years, 4 months, 15 days, 3 hours, 25 minutes and 20 seconds ago.
SELECT 'Created '
+ CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 30 / 12 AS NVARCHAR(50)) + ' years, '
+ CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 30 % 12 AS NVARCHAR(50)) + ' months, '
+ CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 30 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.'
Calculating time difference in Minutes Hours Days Weeks Months Years Calculating time difference in Minutes Hours Days Weeks Months Years Reviewed by Bhaumik Patel on 8:19 PM Rating: 5