Display posted on date and time like1 hr ago or 30 min ago

I want to show the posted date like 1 hr ago, or 30 min ago. This is my code:

declare @date datetime ='2014-07-24 11:50:22.033';
SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN 
  CASE WHEN DATEPART(HH,@date)=DATEPART(HH,GETDATE()) THEN
      CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS NVARCHAR(50)) +' Minutes ago '
     ELSE 
      CAST(DATEDIFF(HOUR, @date, GETDATE()) AS NVARCHAR(50)) + ' Hours ago  ' END
 ELSE
  CAST(DATEDIFF(DAY, @date, GETDATE()) AS NVARCHAR(50)) + ' Days ago.. ' 
END AS Postedon

I am getting a problem when the posted time is 11:50 and the server time is 12:10. In that case, I would like to show "20 min ago", but my code showing 1 hr ago.

As I said in the comments above, this should really be formatted in the application layer, where you are controlling the presentation to the user.

The issue lies with the way SQL handles the DATEDIFF function, in that comparing the HOUR difference between 08:59 and 09:00 will result in 1 hour, because the hour has ticked over and there is 1 hour difference between 8 and 9.

My solution may not be the most elegant, however it uses difference in MINUTE to get the number of hours passed since posting and the difference in HOUR to get the number of days.

declare @date datetime ='2014-07-23 09:50:22.033';
    SELECT 
      CASE 
        WHEN DATEDIFF(SECOND, @date, GETDATE()) < 60 THEN ' Just Posted' 
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS VARCHAR(10)) + ' Minutes ago'
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, @date, GETDATE())/60) AS VARCHAR(10)) + ' Hours ago'
        ELSE CAST(FLOOR(DATEDIFF(HOUR, @date, GETDATE())/24) AS VARCHAR(10)) + ' Days ago'
    END AS Postedon

StackExchange Database Administrators Q#72221, answer score: 6