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 -----------------------------------------------------------------------------
Comments
Post a Comment