SQL DateTime Datediff

Question

Trying to return the Date stored in the Database in the form

Days: Hours : Minutes

But the SQL Code below does not seem to work well.

 select 
 CONVERT(VARCHAR(40),DATEDIFF(minute, MAX(sends), GETDATE())/(24*60))  + '<b>days:</b> ' + CONVERT(VARCHAR(40), DATEDIFF(minute,  MAX(sends), GETDATE())%(24*60)/60) 
+ ' <b>hours:</b> '  + CONVERT(VARCHAR(40), DATEDIFF(minute,  MAX(sends), GETDATE())%60) + '<b>min.</b>'  as sends FROM Table_Name

What I am trying to do is get the Age of a message i.e how long the message has been in the specific database table. And I would like to be able to do it in SQL, not in my application.

Answer 1

I'd return the total seconds (the smallest granularity you care about) and let the application code format it. That'll scale better long term and is usually easier to write as well.

Answer 2

Perhaps instead of doing caculations by hand, you read about the datediff function and have it do the work for you.

Or better yet, just get minutes and do the formatting in php (or whatever you're using).