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

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

In previous articles I explained SQL Server Get only date or time from date time field,  SQL Server get list of procedures which contains table name, 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 datetime 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 thenConvert(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 thenConvert(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 thenConvert(Varchar,DateDiff(dd,@givenDate,@curDate)) + ' days ago'                                                                                  when DateDiff(ww,@givenDate,@curDate) <= 1 thenConvert(Varchar,DateDiff(ww,@givenDate,@curDate)) + ' week ago'                                                                                   when DateDiff(ww,@givenDate,@curDate) > 1 and DateDiff(ww,@givenDate,@curDate) <= 4 thenConvert(Varchar,DateDiff(ww,@givenDate,@curDate)) + ' weeks ago'                                                                                  when DateDiff(mm,@givenDate,@curDate) <= 1 thenConvert(Varchar,DateDiff(mm,@givenDate,@curDate)) + ' month ago'                                                                                  when DateDiff(mm,@givenDate,@curDate) > 1 and DateDiff(mm,@givenDate,@curDate) <= 12 thenConvert(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