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
  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]