CODEWITHSUNDEEP
sqlsql-server-2008
Joe
Joe

Reputation: 173

Calculate and format time in SQL Server

I execute the following command in SQL Server 2008 and it does not give me the desired results.

 RTRIM(avg(ProcTime)/3600) + ':'  + RIGHT(('0'+RTRIM(avg(MTD_ProcTime) % 3600) / 60),2)

Let's say I get 2:5 instead of 2:05, it drops the zero. How do I get that zero in front of the 5 ?

Upvotes: 0

Views: 55

Answers (2)

Tom Studee
Tom Studee

Reputation: 10452

What you want is the two zeros in your RIGHT(('00'+ ... function, like this:

RTRIM(avg(ProcTime)/3600) + ':'  + 
RIGHT(('00'+RTRIM(avg(MTD_ProcTime) % 3600) / 60),2)

That way you end up with right 2 chars of 00N, or 0N.

Upvotes: 0

Dan Pichelman
Dan Pichelman

Reputation: 2332

try 

RTRIM(avg(ProcTime)/3600) + ':'  + RIGHT(('0'+RTRIM(avg(MTD_ProcTime) % 3600 / 60)),2)

I moved the ) from after 3600 to after 60

Upvotes: 1

Related Questions