Formating Time output to hh:mm:ss

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Formating Time output to hh:mm:ss

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-05-21 : 06:10:15

I am trying to format the output of a calculation that takes an endtime field from a starttime field. And then I need the output to be hh:mm:ss

I started to use a DATEDIFF but I cant see how I would output in that format.

As below:(DATEDIFF(HOUR,StartTime,EndTime)) As Hrs.
If this was for 1 hour it would show as 1 but I need 01:
In fact what I really need is the result to be 01:15:20

There must be an easier way.

Help please!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-21 : 08:39:20

This should work:

declare @starttime time(0)
declare @endtime time(0)

set @starttime = '2011-05-21 06:58:47.997'
set @endtime = '2011-05-21 08:10:47.997'

select dateadd(MI,-1* datediff(MI,'00:00:00:00',@starttime),@endtime) elapsedtime

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-05-21 : 09:42:17

Perfect.

Thank you kindly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources