How To Display Date And Time In Duration (Such As One Hour Ago) In SQL Server

Introduction

 

In this post, I am going to explain the SQL statements which are used to to get the date and time difference in a valid format; for example, one hour ago, 30 minutes ago, one week ago, one month ago, and so on.

Implementation

When you work with any data-driven application, sometimes, you need to fetch the date and time from the database and display in your Web/Windows form based on your requirement. So today, I will show you the proper way and format to get/fetch the date and time from your database and display it in a proper manner.
First, I will create one SQL function that will return the string with the date and time.

 

SQL Function

 

CREATE FUNCTION GetDateFormat
(
@FROM_DATE DATETIME ,
     @TO_DATE DATETIME

 )
 RETURNS
VARCHAR(100)
AS
BEGIN
DECLARE @Date
AS
VARCHAR(100)
  SELECT  @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1
 THEN'1 min ago'
WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60  THEN
CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE, @TO_DATE))  + ' mins ago'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1  THEN
CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE))  + ' hour ago'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 24  THEN
CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE))  + ' hrs ago'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1  THEN
CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE))  + ' day ago'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 7  THEN
CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE))  + ' days ago'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1  THEN
CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE))  + ' week ago'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 4  THEN
CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE))  + ' weeks ago'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1  THEN
CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE))  + ' month ago'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 12  THEN
CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE))  + ' mnths ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1  THEN
CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE))  + ' year ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1  THEN
CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE))  + ' yrs ago'END
RETURN @Date
END

 Now, let us see how you can use this SQL function. You just need to pass the "from" and "to" date in the created function as parameters.

DECLARE @FromDate DATETIME
SET @FromDate ='2018-08-22 11:48:49.830'
select dbo.GetDateFormat(@FromDate,GETDATE()) [Date]