Click here to Skip to main content
14,664,400 members
Articles » Database » Database » SQL Server
Tip/Trick
Posted 12 Oct 2013

Tagged as

Stats

10.1K views
2 bookmarked

Display Last Activity Date and Time in Microsoft SQL

Rate this:
4.50 (2 votes)
Please Sign up or sign in to vote.
4.50 (2 votes)
12 Oct 2013CPOL
Display last activity Date and Time smartly like Google and Facebook in Microsoft SQL

Introduction

Many times, there is a requirement to display last activity date and time smartly for last logged in, last message, last chat, etc. So, I’ve created a function in Microsoft SQL, which will return last activity date or time. This function will compare last date time with current date time that is ‘GETDATE()’ and will return result accordingly.

Background

Many times, we need to display dates in different formats. So I've created a SQL function to display Date or Time smartly, which can be used to show last activity status. If you have noticed when we chat or when we Google some information on internet, then Date or Time is displayed, like the information or news is updated so and so time, last comments on chat, last logged in, etc. Normal Date format is described in my blog. Click on the following link to read:

Image 1

Using the Code

Output will be displayed in second, minute(s), hour(s), day(s), month(s), and in year(s) as shown below:

——————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(GETDATE()-1)

Result: day ago (11-10-2013)

——————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(GETDATE()-2)

Result: 2 days ago (11-10-2013)

——————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(CONVERT(DATETIME,’2013-10-12 12:04:46.323'))

Result: 2 minutes ago

——————————————————————————————————————————————

SQL Function to Display Last Activity Date or Time

CREATE FUNCTION [dbo].[F_GetLastActiveDateTime]
(
	-- Add the parameters for the function here
	@lastActiveDate Datetime
)
RETURNS varchar(30)
AS
BEGIN
  DECLARE @LastActivity varchar(100)
  SET @LastActivity = '';
  -- Add the T-SQL statements to compute the return value here
  DECLARE @today datetime, @nowLastActiveDate datetime
  DECLARE @years int, @months int, @days int, @hours int, _
  @minutes int, @seconds int, @h int, @m int, @s int  
  
  SELECT @today = GETDATE() 
  SELECT @nowLastActiveDate = DATEADD(year, _
  DATEDIFF(year, @lastActiveDate, @today), @lastActiveDate)            
  
  SELECT @years = DATEDIFF(year, @lastActiveDate, @today) - _
  (CASE WHEN @nowLastActiveDate > @today THEN 1 ELSE 0 END) 
  SELECT @months = MONTH(@today - @nowLastActiveDate) - 1 
  SELECT @days = DAY(@today - @nowLastActiveDate) - 1 
  
  SELECT @h = DATEDIFF(HOUR, @lastActiveDate, @today)
  SELECT @m = DATEDIFF(MINUTE, @lastActiveDate, @today)
  SELECT @s = DATEDIFF(SECOND, @lastActiveDate, @today)
  
  SET @hours = (@h%24)
  SET @minutes = (@m%60)
  SET @seconds = (@s%60)                 
      
	  
SET @LastActivity =
	(CASE 
		WHEN @years = 1 THEN ' year ago _
		(' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @years > 1 THEN convert(varchar(3),@years) + _
		' years ago (' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @months > 1 THEN ' month ago (' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @months = 1 THEN convert(varchar(3),@months) + _
		' months ago (' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @days = 1 THEN ' day ago (' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @days > 1 THEN convert(varchar(3),@days) + _
		' days ago (' +  convert(varchar, @lastActiveDate, 105) + ')'
		WHEN @hours = 1 THEN ' hour ago'
		WHEN @hours > 1 THEN convert(varchar(3),@hours) + ' hours ago'
		WHEN @minutes = 1 THEN ' minute ago'
		WHEN @minutes > 1 THEN convert(varchar(3),@minutes) + ' minutes ago'
		WHEN @seconds = 1 THEN ' second ago'
		WHEN @seconds > 1 THEN convert(varchar(3),@seconds) + ' seconds ago'
		ELSE convert(varchar, @lastActiveDate, 105)
	 END)
			 
	 RETURN @LastActivity; 

END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Yugal Pandya
Technical Lead
India India
Software Professional experience since 2006 in ASP.NET, VB.NET, C# .NET, SQL SERVER, ORACLE, MS ACCESS, Web Services, HTML, JQUERY and CRYSTAL REPORTS.

Expertise in developing web and Desktop applications.

Visit http://yugalpandya.wordpress.com for more information.

Comments and Discussions

 
-- There are no messages in this forum --