Show Time Difference in Minute Ago, Hour Ago, Day Ago, Week Ago, Month Ago

SQL Server - Show Time Difference in Minute Ago, Hour Ago, Day Ago, Week Ago, Month Ago

Introduction

Here I will explain SQL Server query to get date time difference in minute ago, hour ago, day ago, week ago or month ago format in SQL Server 2008 / 2005 or how to get date time format in minute ago, hour ago, day ago, week ago or month ago in SQL Server 2008 / 2005.

Description

 SQL Server can function return multiple values, SQL Server update multiple tables with inner joins, SQL Server difference between view and stored procedure and many articles relating to SQL Server. Now I will explain how to write query to get date time in minute ago, hour ago, day ago, week ago or month ago format in SQL Server.

To get date-time field in time ago format first we need to create function like as shown below

CREATE FUNCTION fngettimeinagoformat(@givenDate DateTime,@curDate DateTime)                      RETURNS Varchar(100)                      AS                      BEGIN                      declare @Date as Varchar(100)                    select @Date =                     case                                              when DateDiff(mi,@givenDate,@curDate) <= 1 then '1 min ago'                                                                          when DateDiff(mi,@givenDate,@curDate) > 1 and DateDiff(mi,@givenDate,@curDate) <= 60 then  Convert(Varchar,DateDiff(mi,@givenDate,@curDate)) + ' mins ago'                                                                          when DateDiff(hh,@givenDate,@curDate) <= 1 then  Convert(Varchar,DateDiff(hh,@givenDate,@curDate)) + ' hour ago'                                                                          when DateDiff(hh,@givenDate,@curDate) > 1 and DateDiff(hh,@givenDate,@curDate) <= 24 then  Convert(Varchar,DateDiff(hh,@givenDate,@curDate)) + ' hrs ago'                                                                          when DateDiff(dd,@givenDate,@curDate) <= 1 then  Convert(Varchar,DateDiff(dd,@givenDate,@curDate)) + ' day ago'                                                                                  when DateDiff(dd,@givenDate,@curDate) > 1 and  DateDiff(dd,@givenDate,@curDate) <= 7 then  Convert(Varchar,DateDiff(dd,@givenDate,@curDate)) + ' days ago'                                                                                  when DateDiff(ww,@givenDate,@curDate) <= 1 then  Convert(Varchar,DateDiff(ww,@givenDate,@curDate)) + ' week ago'                                                                                   when DateDiff(ww,@givenDate,@curDate) > 1 and DateDiff(ww,@givenDate,@curDate) <= 4 then  Convert(Varchar,DateDiff(ww,@givenDate,@curDate)) + ' weeks ago'                                                                                  when DateDiff(mm,@givenDate,@curDate) <= 1 then  Convert(Varchar,DateDiff(mm,@givenDate,@curDate)) + ' month ago'                                                                                  when DateDiff(mm,@givenDate,@curDate) > 1 and DateDiff(mm,@givenDate,@curDate) <= 12 then  Convert(Varchar,DateDiff(mm,@givenDate,@curDate)) + ' mnths ago'                                                                                  when DateDiff(yy,@givenDate,@curDate) <= 1 then  Convert(Varchar,DateDiff(yy,@givenDate,@curDate)) + ' year ago'                                                                                  when DateDiff(yy,@givenDate,@curDate) > 1 then  Convert(Varchar,DateDiff(yy,@givenDate,@curDate)) + ' yrs ago'                                                                                  end                       return @Date                    END

Once we create above function we need to pass two date parameters to execute the query like as shown below

DECLARE @givendate DATETIME SET @givendate ='2013-09-06 06:04:56.517' select dbo.fngettimeinagoformat(@givendate,GETDATE())

If we run above query output will be like as shown below

Output

I am running above function in datetime format table that sample will be like this