Skip to main content

Posts

Showing posts from May, 2017

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(DATE…