Display Last Activity Date and Time 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:

• Different Date and Time Formats in Microsoft SQL

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