Tuesday, December 13, 2011

Show the date ,hour and day with message in sql server.


select top(3) IdeaID,Comments ,
Case when (CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))>1439)
then CONVERT(varchar(20), CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))/1440 ) +' days'
 when (CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))>=60)
then CONVERT(varchar(20),CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))/60) + ' hours'
else
CONVERT(varchar(20),CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))) + 'minutes'
End   from comments order by CommentedOn desc

No comments:

Post a Comment