13,042,630 members (77,055 online)
Rate this:
See more:
Hi,

I tried to get recent comments stored procedure and I got it.Now I need to get recent comments title, content with last modified time log like,

I want output like this.

1 hours 25 min ago

```create proc recentComments
as
begin
select title,content from userComment order by  lastModifiedOn Desc
end```
Posted 7-Mar-13 17:50pm

Rate this:

## Solution 1

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```
v3
Maciej Los 8-Mar-13 15:22pm

It should works, +5!
Sandhanamurali 9-Mar-13 6:08am

hi,
thanks for solving the stored procedure but its getting error like that Incorrect syntax near 'DateDiff'.
Andrew Cherednik 9-Mar-13 6:57am

yes, there was a typo. Forgot the closing bracket. Please check again
Sandhanamurali 20-Mar-13 3:33am

thank you dude now its working but not coming like two days ago ,one days ago can
you help me to do.
Andrew Cherednik 20-Mar-13 19:01pm

I have updated the solution for you
Sandhanamurali 21-Mar-13 3:44am

thank you its working now

Top Experts
Last 24hrsThis month
 Karthik Bangalore 130 Satya Prakash Swain 130 OriginalGriff 105 ppolymorphe 105 Richard MacCutchan 75
 OriginalGriff 4,255 RickZeeland 1,834 F-ES Sitecore 1,483 ppolymorphe 1,474 Dave Kreskowiak 1,309