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
  1. CREATE FUNCTION GetDateFormat  
  2.     (  
  3.       @FROM_DATE DATETIME ,  
  4.       @TO_DATE DATETIME  
  5.     )  
  6. RETURNS VARCHAR(100)  
  7. AS  
  8.     BEGIN                       
  9.         DECLARE @Date AS VARCHAR(100)                     
  10.         SELECT  @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1  
  11.                              THEN '1 min ago'  
  12.                              WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1  
  13.                                   AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60  
  14.                              THEN CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE,  
  15.                                                             @TO_DATE))  
  16.                                   + ' mins ago'  
  17.                              WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1  
  18.                              THEN CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE,  
  19.                                                             @TO_DATE))  
  20.                                   + ' hour ago'  
  21.                              WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) > 1  
  22.                                   AND DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 24  
  23.                              THEN CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE,  
  24.                                                             @TO_DATE))  
  25.                                   + ' hrs ago'  
  26.                              WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1  
  27.                              THEN CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE,  
  28.                                                             @TO_DATE))  
  29.                                   + ' day ago'  
  30.                              WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) > 1  
  31.                                   AND DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 7  
  32.                              THEN CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE,  
  33.                                                             @TO_DATE))  
  34.                                   + ' days ago'  
  35.                              WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1  
  36.                              THEN CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE,  
  37.                                                             @TO_DATE))  
  38.                                   + ' week ago'  
  39.                              WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) > 1  
  40.                                   AND DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 4  
  41.                              THEN CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE,  
  42.                                                             @TO_DATE))  
  43.                                   + ' weeks ago'  
  44.                              WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1  
  45.                              THEN CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE,  
  46.                                                             @TO_DATE))  
  47.                                   + ' month ago'  
  48.                              WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) > 1  
  49.                                   AND DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 12  
  50.                              THEN CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE,  
  51.                                                             @TO_DATE))  
  52.                                   + ' mnths ago'  
  53.                              WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1  
  54.                              THEN CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE,  
  55.                                                             @TO_DATE))  
  56.                                   + ' year ago'  
  57.                              WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1  
  58.                              THEN CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE,  
  59.                                                             @TO_DATE))  
  60.                                   + ' yrs ago'  
  61.                         END                        
  62.         RETURN @Date                     
  63.     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.
 
SQL Query/Statment
  1. DECLARE @FromDate DATETIME  
  2. SET @FromDate ='2018-08-22 11:48:49.830'  
  3. select dbo.GetDateFormat(@FromDate,GETDATE()) [Date]  
Output
 
  
 
 
 
Summary 
 
You can see in the above SQL statement that using this function, you can fetch the date and time from the database in a format like "1 hr ago" or "30 min ago" and so on. 

I hope this post is helpful to you. If you have any question regarding this SQL statement, please leave your comments below.