Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 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
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

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


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

Get the latest Asp.net, C#.net, 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

10 comments :

Manoj said...

Hey how to use this with SqlDataSource for GridView in asp.net

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

superup..

Sql Questions said...

Good

Sql Interview Questions said...

Good Topic

Unknown said...

This works great. Thanks alot

sakinala said...

thanks

heroman said...

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

Rajesh Raju said...

Nice,Very Useful

Anonymous said...

Excellent

Give your Valuable Comments

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

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