SQL Server

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.  )   
  7.  RETURNS  
  8. VARCHAR(100)    
  9. AS  
  10. BEGIN  
  11. DECLARE @Date  
  12. AS  
  13. VARCHAR(100)      
  14.   SELECT  @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1   
  15.  THEN'1 min ago'  
  16. WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1  AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60  THEN  
  17. CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE, @TO_DATE))  + ' mins ago'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1  THEN  
  18. 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  
  19. CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE))  + ' hrs ago'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1  THEN  
  20. 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  
  21. CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE))  + ' days ago'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1  THEN  
  22. 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  
  23. CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE))  + ' weeks ago'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1  THEN  
  24. 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  
  25. CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE))  + ' mnths ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1  THEN  
  26. CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE))  + ' year ago'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1  THEN  
  27. CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE))  + ' yrs ago'END  
  28. RETURN @Date  
  29. 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.
  1. DECLARE @FromDate DATETIME    
  2. SET @FromDate ='2018-08-22 11:48:49.830'    
  3. select dbo.GetDateFormat(@FromDate,GETDATE()) [Date]   
 
Membership not found