aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Sep 5, 2013

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.


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)                     
declare @Date as Varchar(100)                   
select @Date =                    
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'                                                                                 
return @Date                   
Once we create above function we need to pass two date parameters to execute the query like as shown below

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


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

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


manoj kumar said...

very good

Manoj said...

Hey how to use this with SqlDataSource for GridView in

Unknown said...

Hi Suresh,
In procedures what is the difference between set and select keyword?.whether they are same or not?.Explain it briefly.

Thanks in advance

Harshad said...


Sql Questions said...


Sql Interview Questions said...

Good Topic

Unknown said...

This works great. Thanks alot

sakinala said...


heroman said...

How to get one or some new rows without older rows?

Rajesh Raju said...

Nice,Very Useful

Anonymous said...


Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.