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
2
-
You should almost certainly do this in your front-end code (C#, VB, whatever) rather than a SQL query.LukeH– LukeH2009-09-14 10:28:33 +00:00Commented Sep 14, 2009 at 10:28
-
@surya: then you should do the polite and proper thing and accept the best answer / the one that really solved you problem. To accept the answer, please click on the check mark to the left of the answer below the "0" with the up- and down-arrow. If someone helps you - please be so kind as to accept the answer.marc_s– marc_s2009-09-14 21:28:42 +00:00Commented Sep 14, 2009 at 21:28
Add a comment
|
2 Answers
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
Comments
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)'
