SQL Server: Friendly Time Differences

Ok, so I always like the Facebook smart date displays, like

posted by Bob Loblaw about 12 minutes ago

So I set about recreating this on my own sites in SQL Server.  Here is a fairly easy way to produce this kind of friendly time differences.




Select *
,iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days  ',
''
)+
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) % 24 as varchar(90))+' hours  ',
''
)+
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) % 60 as varchar(90))+' minutes  ',
''
)+
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) % 60 as varchar(90))+' seconds ago.',
''
)
[friendlytime]
from errorlog
--where errdate > DATEADD(HOUR, -1, GETDATE())
order by errdate




This produces a nice, friendly time difference that is better than a list of raw dates for human interpretation.

errdate	page	errmessage	friendlytime
1961-06-17 11:07:21.820	TEST	THIS IS A TEST ERROR MESSAGE	18993 days  0 hours  20 minutes  36 seconds ago.
2010-06-17 11:06:05.470	TEST	THIS IS A TEST ERROR MESSAGE	1096 days  0 hours  21 minutes  52 seconds ago.
2012-06-17 11:04:41.123	TEST	THIS IS A TEST ERRORMESSAGE	365 days  0 hours  23 minutes  16 seconds ago.
2013-06-17 11:07:59.197	TEST	THIS IS A TEST ERROR MESSAGE	20 minutes  58 seconds ago.

Alternately, if you don't care to display all the way down to seconds when the time span is many days, you can nest it like this to only show the most relevant time gap.




iif(
datediff(DAY, errdate, getdate()) >0 ,
cast(datediff(DAY, errdate, getdate()) as varchar(90))+' days ago',
iif(
datediff(HOUR, errdate, getdate()) >0 ,
cast(datediff(HOUR, errdate, getdate()) as varchar(90))+' hours ago',
iif(
datediff(MINUTE, errdate, getdate()) >0 ,
cast(datediff(MINUTE, errdate, getdate()) as varchar(90))+' minutes ago',
iif(
datediff(SECOND, errdate, getdate()) >0 ,
cast(datediff(SECOND, errdate, getdate()) as varchar(90))+' seconds ago.',
'now'
)
)
)
) [friendlytime2]


This produces a result as follows:

18993 days ago

1096 days ago

365 days ago

38 minutes ago

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.