query for datetime

Question

I want to display the time like mr.xxx login 1 minit ago or 1 hor ago or 2 days ago or 1 month ago or 1year ago. how can i write the query to display like that [for example in our stackoverflow we see mr.xx joined 5 days ago. he ask question 2 minits ago] please help me

Answer 1

I expect you would keep a log of when the users last logged in within a SQL table in your database, which can be used for audit history.

With that information you could create a stored proc, which could retrieve information when that user last logged in.

e.g. Example SQL for last logged in (IN DAYS)

DECLARE @LoggedIn AS DATETIME
SET @LoggedIn = '01 Apr 2009'

SELECT DATEDIFF(dd, @LoggedIn, GETDATE()) AS 'Last Logged In (DAYS)'

Answer 2

This example should work on any version of SQL Server.

It's more verbose than it really needs to be to make it clearer how it works.

--create test data
create table #t
(id int
,lastTime datetime
)

insert #t
select 1,dateadd(mi,-1,getdate())
union select 2,dateadd(hh,-1,getdate())
union select 3,dateadd(dd,-1,getdate())
union select 4,dateadd(mm,-1,getdate())
union select 5,dateadd(yy,-1,getdate())
union select 6,dateadd(yy,-5,getdate())
union select 7,NULL

-- carry out formatting to handle pluralisation
SELECT id
       ,ISNULL(lastAction,'Never') 
        + CASE WHEN lastVal > 1
               THEN 's ago'
               WHEN lastVal = 1
               THEN ' ago'
               ELSE ''
        END
FROM
(
        -- Use coalesce to select the first non-null value from the matrix
        SELECT id
               ,COALESCE(CAST(years   as VARCHAR(20)) + ' year'
                        ,CAST(months  as VARCHAR(20)) + ' month'
                        ,CAST(days    as VARCHAR(20)) + ' day'
                        ,CAST(hours   as VARCHAR(20)) + ' hour'
                        ,CAST(minutes as VARCHAR(20)) + ' minute'
                        ,CAST(secs    as VARCHAR(20)) + ' second'
                        ) as lastAction
               ,COALESCE(years
                        ,months
                        ,days
                        ,hours
                        ,minutes
                        ,secs
                        ) as lastVal
        FROM                
        (
                -- create a matrix of elapsed time
                SELECT id
                       ,datediff(ss,lastTime,getdate()) secs
                       ,NULLIF(datediff(mi,lastTime,getdate()),0) minutes
                       ,NULLIF(datediff(hh,lastTime,getdate()),0) hours
                       ,NULLIF(datediff(dd,lastTime,getdate()),0) days
                       ,NULLIF(datediff(mm,lastTime,getdate()),0) months
                       ,NULLIF(datediff(yy,lastTime,getdate()),0) years
                from #t
        ) as X
) AS Y