You need to use DateDiff
Here is an example to use hours and minutes:
create proc recentComments
as
begin
select title, content,
Convert(VARCHAR(10), DateDiff(hour, GetDate(), lastModifiedOn)) +
' hours ' +
Convert(VARCHAR(10), DateDiff(minutes, GetDate(), lastModifiedOn) - DateDiff(hour, GetDate(), lastModifiedOn) * 60) +
' minutes' AS timeElapsed from userComment order by lastModifiedOn Desc
end
Here is an example to use days (when the last comment was more than 24 hours ago):
create proc recentComments
as
begin
select title, content,
Case When DateDiff(hour, GetDate(), lastModifiedOn) > 24 Then
Convert(VARCHAR(10), DateDiff(day, GetDate(), lastModifiedOn)) +
' days' Else
Convert(VARCHAR(10), DateDiff(hour, GetDate(), lastModifiedOn)) +
' hours ' +
Convert(VARCHAR(10), DateDiff(minutes, GetDate(), lastModifiedOn) - DateDiff(hour, GetDate(), lastModifiedOn) * 60) +
' minutes' End AS timeElapsed from userComment order by lastModifiedOn Desc
end