Calculate and format time in SQL Server

Question

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 ?

Can you state what you're doing and that what 2:5 or 2:05 means. Also what does ProcTime & MTD_ProcTime contain. — Preet Sangha
– Preet Sangha, Commented Jun 6, 2013 at 0:45

Dan Pichelman · Accepted Answer · 2013-06-06 00:48:36Z

1

try

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

I moved the ) from after 3600 to after 60

answered Jun 6, 2013 at 0:48

Dan Pichelman

2,3422 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Studee · Accepted Answer · 2013-06-06 13:50:08Z

0

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.

answered Jun 6, 2013 at 0:49

Tom Studee

10.5k4 gold badges42 silver badges42 bronze badges