Display Last Activity Date and Time Smartly like Google and Facebook in MS SQL

Posted: October 12, 2013 in SQL

Introduction

Many times there is requirement like to display last activity date and time smartly for last logged in, last message, last chat etc. So, I’ve created a function in MS 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.

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

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

Screenshot

Function smart Date or Time like Google and Facebook in MS SQL

Function to display smart Date or Time like Google and Facebook in MS SQL

Using the Code

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s